[Sqlrelay-discussion] FreeTDS bind incorrectly escaping strings
Brought to you by:
mused
|
From: Michael A. <mi...@mi...> - 2008-03-26 11:26:03
|
Hi there,
Ran into an interesting problem today... it appears that bound input
variables are escaped incorrectly when running against SQL Server.
Basically it is escaping quotes with a backslash (unix style), not
double quotes (MS style).
E.g. the following PHP code...:
$sql = "SELECT * FROM tblPatron WHERE FirstName = @first_name";
$args['first_name'] = "O'Rourke";
$q = sqlrcon_alloc("localhost", $port, $socket, $username,
$password, 1, 1);
$c = sqlrcur_alloc($q);
sqlrcur_prepareQuery($c, $sql);
foreach($args as $a => $b)
sqlrcur_inputbind($c, $a, $b);
$o = sqlrcur_executequery($c);
if ($o != 1) throw new Exception("Error with query: " .
sqlrcur_errorMessage($c));
for ($i=0; $i<sqlrcur_rowcount($c); $i++)
$result[] = (object) sqlrcur_getrowassoc($c, $i);
sqlrcur_free($c);
sqlrcon_free($q);
This results in the following error:
Error with query: Server message:
severity(0)
number(102)
state(1)
line(1)
Server Name:
HAMLET\SQLEXPRESS
Procedure Name:
Error: Incorrect syntax near 'Rourke'.
So the SQL passed to the DB would be: SELECT * FROM tblPatron WHERE
FirstName = 'O\'Rourke'; SQL server doesn't treat the backslash
specially so strings ends before the R.
It should be: SELECT * FROM tblPatron WHERE FirstName = 'O''Rourke';
If I set $args['first_name'] = "O''Rourke" (two single quotes) then I
get: Error: Incorrect syntax near '\'.
So the SQL passed to the DB would be: SELECT * FROM tblPatron WHERE
FirstName = 'O\'\'Rourke'; so this time the statement ends at the second \
It should be: SELECT * FROM tblPatron WHERE FirstName = 'O''''Rourke';
Am I doing something wrong? The only relevant info I could find is that
under FreeTDS connections the binds are faked, but nothing how strings
are escaped.
Thanks in advance, SQLR has made a massive (~ 5x) improvement in
database access times here.
--Michael Adams
|