" ' " problem once again

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

" ' " problem once again

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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'] . "'";
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
riley
Forum Commoner
Posts: 45
Joined: Thu May 02, 2002 6:31 pm

Post 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);
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post 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) ;
Post Reply