Page 1 of 1

" ' " problem once again

Posted: Thu Apr 17, 2003 9:33 pm
by valen53
" ' " problem, actually i have test to insert " ' " into mysql and mssql, mysql was accept " ' " , but mssql was come out error, mssql cannot recognice " '' " but mysql can be inserted.

so, anyone know about insert " ' " into mssql ?
i have try addslashes() and htmlspecialchars() also cannot work. so any other method can introduce ??
thank reply

Posted: Thu Apr 17, 2003 10:14 pm
by McGruff
addslashes() on the way in and stripslashes() on the way out should deal with single quotes.

Are you enclosing the column vars in single quotes in your insert query?

For example:

$mysql = "INSERT INTO table SET column='$var'";

..or if you are referencing an array:

$mysql = "INSERT INTO table SET column='" . $array['key'] . "'";

Posted: Thu Apr 17, 2003 10:32 pm
by valen53
this is my sample code
$aa = addslashes($aa) ;

echo $aa;
$insert = "insert into TEMP_EMP2_leave(emp_id)values('$aa')";
$result = mssql_query($insert) ;
--------------------------------
$aa - input text
i put $aa = doesn't
echo $aa = doesn\\''t
error message
Warning: mssql_query() [function.mssql-query]: message: Line 1: Incorrect syntax near 't'. (severity 15) in C:\Apache Group\Apache\htdocs\leave\aa.php on line 10
Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark before the character string ')'. (severity 15) in C:\Apache Group\Apache\htdocs\leave\aa.php on line 10

Posted: Fri Apr 18, 2003 5:12 am
by twigletmac
It doesn't work in MS SQL because using the backslash as an escape character is actually not proper SQL - in databases like MS SQL you need to use the single quote as an escape character for single quotes within single quoted strings so you have '' instead of ''.

Instead of using addslashes() you can just do (I couldn't find a specific function for this):

Code: Select all

$aa = str_replace("'", "''", $aa);
For more info you should check out:
http://msdn.microsoft.com/library/defau ... ide_27.asp
http://www.php.net/manual/en/function.str-replace.php

Mac

Posted: Fri Apr 18, 2003 7:27 am
by riley
http://www.php.net/manual/en/function.h ... lchars.php

htmlspecialchars($data, ENT_QUOTES);
works for MS Sql



'&' (ampersand) becomes '&'

'"' (double quote) becomes '"' when ENT_NOQUOTES is not set.

''' (single quote) becomes ''' only when ENT_QUOTES is set.

'<' (less than) becomes '<'

'>' (greater than) becomes '>'

Example 1. htmlspecialchars() example

$new = htmlspecialchars("<a href='test'>Test</a>", ENT_QUOTES);

Posted: Fri Apr 18, 2003 7:37 am
by twigletmac
riley wrote:http://www.php.net/manual/en/function.h ... lchars.php

htmlspecialchars($data, ENT_QUOTES);
Surely it's better to use proper escape characters rather than HTML entities? Save a bit of space in the database at least - 2 characters instead of 5 for the single quotes, plus you'll also not have converted a bunch of characters that don't need it (&, <, > etc.).

Mac

Posted: Mon Apr 21, 2003 2:11 am
by valen53
thankx for twigletmac .......
finally solve the problem ...
b4 that i use
$aa = str_replace("'",' " ',$aa) ;

it cannot solve all the possible problem.
so i change to used
$aa = str_replace("'","''",$aa) ;