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.
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.
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()
.
SQLSTATE[22007]: Invalid datetime format: 210 [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting datetime from binary/varbinary string.
I believe this method can be correct only if it works with every MSSQL datatype.
Commented
Jun 19, 2012 at 17:38
mssql_escape()
function returned is not doing it for me. The text display after doing a select is looking like this 0x4a2761696d65206269656e206c652063686f636f6c6174
thus unreadable.
Commented
Aug 14, 2012 at 18:09
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
preg_replace
? Isn't the str_replace
sufficient?
empty($value)
will return true
not only for ''
, but also for null
, 0
and '0'
! You would return an empty string in all those cases.
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.
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.
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);
}
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
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:
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.
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()
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 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 ;)
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));
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<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);
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;
}
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
.