How to escape strings in SQL Server using PHP

Question

I m looking for the alternative of mysql real escape string   for SQL Server  Is addslashes   my best option or there is another alternative function that can be used   An alternative for mysql error   would also be useful

User · Answer

For the conversion to get the hexadecimal values in SQL back into ASCII  here is the solution I got on this  using the function from user chaos to encode into hexadecimal   function hexEncode  data        if is numeric  data           return  data       unpacked   unpack  H hex    data       return  0x     unpacked  hex       function hexDecode  hex         str           for   i 0   i lt strlen  hex    i    2           str    chr hexdec substr  hex   i  2         return  str      stringHex   hexEncode  Test String    var dump  stringHex    stringAscii   hexDecode  stringHex   var dump  stringAscii

User · Answer

Why would you bother escaping anything when you can use parameters in your query    sqlsrv query       connection        UPDATE some table SET some field     WHERE other field            array   REQUEST  some field      REQUEST  id        It works right in selects  deletes  updates regardless whether your values parameters are null or not  Make a matter of principle - Don t concatenate SQL and you are always safe and your queries read much better    http   php net manual en function sqlsrv-query php

User · Answer

If you are using PDO  you can use the PDO  quote method

User · Answer

I have been using this as an alternative of mysql real escape string     function htmlsan  htmlsanitize       return  htmlsanitize   htmlspecialchars  htmlsanitize  ENT QUOTES   UTF-8       data    Whatever the value s is    data   stripslashes htmlsan  data

User · Answer

You could roll your own version of mysql real escape string   and improve upon it  with the following regular expression    000 010 011 012 015 032 042 047 134 140   That takes care of the following characters  null  backspace  horizontal tab  new line  carriage return  substitute  double quote  single quote  backslash  grave accent  Backspace and horizontal tab are not supported by mysql real escape string

User · Answer

An answer from 2009-02-22T121000 by user chaos doesn t fit all queries    For example   CREATE LOGIN  0x6f6c6f6c6f  FROM WINDOWS  will give you an exception   PS  look at the SQL nbsp Server driver for PHP  http   msdn microsoft com library cc296181 28v sql 90 29 aspx and the sqlsrv prepare function  which can binds parameters   PSS  Which also didn t help you with the query above

User · Answer

You could look into the PDO Library  You can use prepared statements with PDO  which will automatically escape any bad characters in your strings if you do the prepared statements correctly  This is for PHP 5 only I think

User · Answer

function ms escape string  data            if    isset  data  or empty  data    return             if   is numeric  data    return  data            non displayables   array                 0 0-8bcef                   url encoded 00-08  11  12  14  15                1 0-9a-f                    url encoded 16-31                 x00- x08                   00-08                x0b                         11                x0c                         12                 x0e- x1f                   14-31                    foreach    non displayables as  regex                data   preg replace   regex       data             data   str replace             data            return  data          Some of the code here was ripped off from CodeIgniter   Works well and is a clean solution   EDIT  There are plenty of issues with that code snippet above  Please don t use this without reading the comments to know what those are  Better yet  please don t use this at all  Parameterized queries are your friends  http   php net manual en pdo prepared-statements php

User · Answer

Warning  This function was REMOVED in PHP 7 0 0    http   php net manual en function mssql-query php  For anyone still using these mssql   functions  keep in mind that they have been removed from PHP as of v7 0 0  So  that means you eventually have to rewrite your model code to either use the PDO library  sqlsrv   etc  If you re looking for something with a  quoting escaping  method  I would recommend PDO      Alternatives to this function include  PDO  query    sqlsrv query   and   odbc exec

User · Answer

After struggling with this for hours  I ve come up with a solution that feels almost the best   Chaos  answer of converting values to hexstring doesn t work with every datatype  specifically with datetime columns   I use PHP s PDO  quote    but as it comes with PHP  PDO  quote   is not supported for MS SQL Server and returns FALSE  The solution for it to work was to download some Microsoft bundles    Microsoft Drivers 3 0 for PHP for SQL Server  SQLSRV30 EXE   Download and follow the instructions to install  Microsoft   SQL Server   2012 Native Client  Search through the extensive page for the Native Client  Even though it s 2012  I m using it to connect to SQL Server 2008  installing the 2008 Native Client didn t worked   Download and install    After that you can connect in PHP with PDO using a DSN like the following example   sqlsrv Server 192 168 0 25  Database My Database    Using the UID and PWD parameters in the DSN didn t worked  so username and password are passed as the second and third parameters on the PDO constructor when creating the connection  Now you can use PHP s PDO  quote    Enjoy

User · Answer

addslashes   isn t fully adequate  but PHP s mssql package doesn t provide any decent alternative   The ugly but fully general solution is encoding the data as a hex bytestring  i e    unpacked   unpack  H hex    data   mssql query       INSERT INTO sometable  somecolumn      VALUES  0x     unpacked  hex              Abstracted  that would be   function mssql escape  data        if is numeric  data           return  data       unpacked   unpack  H hex    data       return  0x     unpacked  hex       mssql query       INSERT INTO sometable  somecolumn      VALUES      mssql escape  somevalue             mysql error   equivalent is mssql get last message

User · Answer

It is better to also escape SQL reserved words  For example   function ms escape string  data        if   isset  data  or empty  data           return          if  is numeric  data           return  data        non displayables   array             0 0-8bcef               URL encoded 00-08  11  12  14  15            1 0-9a-f                url encoded 16-31             x00- x08               00-08            x0b                     11            x0c                     12             x0e- x1f               14-31            27              foreach   non displayables as  regex           data   preg replace   regex       data        reemplazar   array                      data   str replace  reemplazar        data       return  data

User · Answer

In order to escape single- and double-quotes  you have to double them up    value    This is a quote   I said   Hi       value   str replace              value        value   str replace              value        query    INSERT INTO TableName   TextFieldName   VALUES     value         etc     and attribution   Escape Character In Microsoft SQL Server 2000

User · Answer

Another way to handle single and double quotes is   function mssql escape  str        if get magic quotes gpc                   str   stripslashes  str             return str replace             str

[php] How to escape strings in SQL Server using PHP?

Examples related to php

Examples related to sql-server

Examples related to escaping

Examples related to input-sanitization