[Sqlrelay-discussion] FreeTDS bind incorrectly escaping strings

[Michael A. <mi...@mi...>]

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