How to escape strings in MSSQL using PHP?

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().

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().

– Click Upvote Feb 22 '09 at 12:20 Yeah, you do a SELECT @@IDENTITY. – chaos Feb 22 '09 at 12:24 Chaos, can you please give an example of an 'escape' function which would take a string as argument and return the hex alternative as a result using the method you gave? I'll accept your answer then – Click Upvote Feb 22 '09 at 12:26 1 Oops, its SELECT SCOPE_IDENTITY()!

– Bryan Rehbein Feb 22 '09 at 19:47 2 @genio: Mmm, great, except it actually is. I don't suppose you'd explain what you consider to be the problem? – chaos Feb 22 '09 at 1:50.

Function ms_escape_string($data) { if (!isset($data) or empty($data) ) return ''; if ( is_numeric($data) ) return $data; $non_displayables = array( '/%00-8bcef/', // url encoded 00-08, 11, 12, 14, 15 '/%10-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.

1 Confirmed to work! Thanks man! – KyleFarris Mar 26 '10 at 21:03 Why do you need the preg_replace?

Isn't the str_replace sufficient? – Gabe Mar 26 '10 at 21:05 gabe: The preg_replace in this case was to allow me to use the ranges afforded to me in regular expressions character classes. There would be a lot more string replaces in this one otherwise.

– genio Mar 26 '10 at 21:07 2 -1. It is not the responsibility of a quoting function to mangle data -- all it should do is make sure the string is in such a format that it can be added to an SQL statement and survive unmodified. – cHao Mar 26 '10 at 6:38 2 Sorry, but this is wrong from the first line of code - 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. – Nux Mar 26 '10 at 13:45.

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.

With some of the halfassed behavior I've seen out of PDO, I'd have to do some serious testing before I trusted it to escape all data correctly. – chaos Feb 22 '09 at 12:11 @Chaos Really? I'm unaware of this.. do you have a link to an article?

– alex Feb 22 '09 at 12:14 What I was thinking of was the trouble this guy on here was having yesterday with PDO. Unrelated transaction stuff, but unimpressive. Combine that with all the history of inadequate data escaping in PHP (php.Net telling people to use addslashes()!

) and I get very suspicious. – chaos Feb 22 '09 at 12:22 Hmmm.. well hopefully they got the escaping portion correct. – alex Feb 22 '09 at 12:33 I love PDO and tried that first, but the one for MSSQL (on Unix, based on dblib) sometimes fails on me (segmentation fault), that's why I resorted to the mssql_escape defined above.

– lapo Feb 22 '097 at 20:58.

I know, litle bit late, but answer from 'Feb 22 '09 at 12:10' by chaos isn`t fit all queries. E. G: "CREATE LOGIN 0x6f6c6f6c6f FROM WINDOWS" will give you exception p.s.

Look at mssql driver for php, msdn.microsoft.com/library/cc296181%28v=... and sqlsrv_prepare function, which can binds params. P.s.s. Which also didn`t helps you with query above ;).

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.