Entry

How do I insert text into a field when the text contains single or double quotes?

Feb 21st, 2000 03:54
Christiaan Schaake, Matt Gregory, Ajith George, Onno Benschop,

Let's say you want to perform the following update:
	$Query = "UPDATE Employees SET (Bio = 'He's a little nervous 
wimp.') WHERE Employee_ID = $Employee_ID";
PHP requires that you add backslashes (\) before every double quote in 
the query.  It does not require that you add backslashes for the single 
quote.  The problem is: MS-SQL, mySQL and every other SQL engine does 
require a backslash before a single quote if you use single quotes 
around the string field you are updating/inserting.  In order to avoid 
this problem use double quotes around the string field values:
	$Query = "UPDATE Employees SET (Bio = \"He's a little nervous 
wimp.\") WHERE Employee_ID = $Employee_ID";
Another solution is to do it this way:
	$Bio = addslashes("He's a little nervous wimp.");
	$Query = "UPDATE Employees SET (Bio = '$Bio') WHERE Employee_ID 
= $Employee_ID";
Oracle:
When using Oracle, single quotes must be replaced by 2 single quotes 
when inserting a record. Oracle does not understand the function of the 
slash to convert special characters to normal. An other problem is that 
double quotes can only be used with column aliasses and not for column 
values. 
To insert a record containing "He's a little nervous wimp." use:
$Bio = str_replace("'","''",$Bio); //convert any single slash to 2 
single slashes.
$Query = "UPDATE Employees VALUES (Bio = '$Bio') WHERE ...