How to avoid rows in SQL Server using PHP?

Question

I am looking for an alternative to mysql_real_escape_string() for SQL Server. Is addslashes() my best option or is there another alternative function that can be used?

An alternative to mysql_error() would also be useful.

Answer 1

addslashes() not quite adequate, but the PHP mssql package does not provide a decent alternative. An ugly but completely general solution is to encode data as a hex criterion, i.e.

 $unpacked = unpack('H*hex', $data); mssql_query(' INSERT INTO sometable (somecolumn) VALUES (0x' . $unpacked['hex'] . ') '); 

It is said that it will 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 of mssql_get_last_message() .

Answer 2

 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; } 

Part of the code here was ripped off from CodeIgniter. Works well and is a clean solution.

EDIT: There are many problems with this piece of code above. Please do not use this without reading the comments to know what it is. Better yet, please do not use this at all. Parameterized queries are your friends: http://php.net/manual/en/pdo.prepared-statements.php

Answer 3

Why bother avoiding 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 correctly when selecting, deleting, updating, regardless of whether your values ​​are null or not. Make a decision in principle. Don’t bind SQL, and you are always safe, and your queries are read much better.

http://php.net/manual/en/function.sqlsrv-query.php

Answer 4

You can take a look at the PDO library . You can use prepared statements with PDOs that automatically delete any bad characters in your lines if you prepared the instructions correctly. I think only for PHP 5.

Answer 5

Another way to handle single and double quotes:

 function mssql_escape($str) { if(get_magic_quotes_gpc()) { $str = stripslashes($str); } return str_replace("'", "''", $str); } 

Answer 6

After struggling with this for several hours, I came up with a solution that seems almost the best.

Chaos response to converting values ​​to hexstring does not work with every data type, in particular datetime columns.

I use PHP PDO::quote() , but since it comes with PHP, PDO::quote() not supported for MS SQL Server and returns FALSE . The solution for his work was to download some Microsoft packages:

• Microsoft Drivers 3.0 for PHP for SQL Server (SQLSRV30.EXE): Download and follow the installation instructions.
• Microsoft® SQL Server® 2012 Parent Client : Search the extensive page for your own client. Despite the fact that in 2012 I use it to connect to SQL Server 2008 (installing my own client in 2008 did not work). Download and install.

After that, you can connect to PHP using PDO using DSN, as shown in the following example:

 sqlsrv:Server=192.168.0.25; Database=My_Database; 

Using the UID and PWD parameters in the DSN did not work, so the username and password are passed as the second and third parameters in the PDO constructor when creating the connection. Now you can use PHP PDO::quote() . Enjoy it.

Answer 7

To avoid single and double quotes, you need to double them:

 $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

Answer 8

Reply from 2009-02-22T121000 created by the user does not match all requests.

For example, "CREATE LOGIN [0x6f6c6f6c6f] FROM WINDOWS" will throw you an exception.

PS: look at the SQL Server driver for PHP, http://msdn.microsoft.com/library/cc296181%28v=sql.90%29.aspx and the sqlsrv_prepare function, which can bind parameters.

PSS: Which also did not help you with the request above;)

Answer 9

Warning: this feature has been removed in PHP 7.0.0.

http://php.net/manual/en/function.mssql-query.php

For those who still use these functions mssql_ *, keep in mind that they were removed from PHP from version 7.0.0. Thus, this means that you will eventually have to rewrite the model code in order to use the PDO library, sqlsrv_ *, etc. If you are looking for something with a "quote / escaping" method, I would recommend PDO.

Alternatives to this function include: PDO :: query (), sqlsrv_query (), and odbc_exec ()

Answer 10

If you use PDO, you can use the PDO::quote method.

Answer 11

It is better to avoid 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; } 

Answer 12

To convert to get hexadecimal values ​​in SQL back to ASCII, here is the solution I got (using a function from user chaos to encode to 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<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); 

Answer 13

I used this as an alternative to mysql_real_escape_string() :

 function htmlsan($htmlsanitize){ return $htmlsanitize = htmlspecialchars($htmlsanitize, ENT_QUOTES, 'UTF-8'); } $data = "Whatever the value is"; $data = stripslashes(htmlsan($data)); 

Answer 14

You can collapse your own version of mysql_real_escape_string (and improve it) with the following regular expression: [\000\010\011\012\015\032\042\047\134\140] . This applies to the following characters: null, backspace, horizontal tab, new line, carriage return, replacement, double quote, single quote, backslash, serious accent. The backspace and horizontal mysql_real_escape_string not supported by mysql_real_escape_string .