Re: Stuck in apostrophe hell
| From: | Philip Thompson | Date: | Tue, 03 Aug 2010 01:46:48 +0000 |
| Subject: | Re: Stuck in apostrophe hell | ||
| References: | 1 2 3 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-47284@lists.php.net to get a copy of this message | ||
On Aug 2, 2010, at 4:42 PM, Bret Hughes wrote:
> I would turn on query logging and see what exactly is making it to mysql.
>
> Niel Archer wrote:
>>> Before I send the following SQL to MySQL from PHP I print it to screen. PHP chokes on
>>> it, but I can paste the exact same query from the screen directly to MySQL and it works just fine.
>>> For example:
>>>
>>> Here's the relevant PHP code:
>>> ======================================
>>> $sql_insert_registration = sprintf("INSERT INTO
>>> Registrations (
>>> Class_ID,
>>> prid,
>>> Registrant,
>>> Company,
>>> Phone,
>>> Email
>>> )
>>> VALUES (
>>> $_POST[Class_ID],
>>> $_POST[prid],
>>> '%s',".
>>> parseNull($_POST['Company']).",
>>> '$_POST[Phone]',
>>> '$_POST[Email]'
>>> )", mysql_real_escape_string($_POST['Registrant']));
>>>
>>> echo "<pre>".$_POST["Registrant"]."</pre>";
>>> echo
>>> "<pre>".mysql_real_escape_string($_POST["Registrant"])."</pre>";
>>> echo "<pre>".$sql_insert_registration."</pre>";
>>>
>>> if (!mysql_query($sql_insert_registration, $con)) { die('Error: ' ..
>>> mysql_error()); ....
>>> ======================================
>>>
>>>
>>> Here's the output:
>>> =======================
>>>
>>> INSERT INTO
>>> Registrations (
>>> Class_ID,
>>> prid,
>>> Registrant,
>>> Company,
>>> Phone,
>>> Email
>>> )
>>> VALUES (
>>> 355,
>>> 257,
>>> 'Brian O\'Brien',NULL,
>>> '612-456-5678',
>>> 'paul_s_johnson@mnb.uscourts.gov'
>>> )
>>> Error: You have an error in your SQL syntax; check the manual that corresponds to your
>>> MySQL server version for the right syntax to use near 'Brien', 'Class registration
>>> confirmation', ' This email ' at line 16
>>> ==================================================
>>>
>>>
>>> Also very oddly if the name "O'Brien" is input into the HTML form with
>>> two apostrophes side by side (O''Brien) then MySQL will take it (but then of course we
>>> have the problem of two apostrophes side by side inserted into the MySQL table). For example:
>>>
>>> ===================================
>>>
>>> INSERT INTO
>>> Registrations (
>>> Class_ID,
>>> prid,
>>> Registrant,
>>> Company,
>>> Phone,
>>> Email
>>> )
>>> VALUES (
>>> 355,
>>> 257,
>>> 'Brian O\'\'Brien',NULL,
>>> '612-456-5678',
>>> 'paul_s_johnson@mnb.uscourts.gov'
>>> )
>>> You have been signed up for the class,
>>> and a confirmation email has been sent to you.
>>> =================================
>>>
>>> Very strange.
>>>
>>> I've checked various PHP variables and cannot figure out. It works fines from
>>> another PHP server that's using the same MySQL database.
>>>
>>> Thanks,
>>>
>>> Paul
>>>
>>
>> Probably needs a double backslash for O'Brien. One to escape the
>> apostrophe and one to escape the backslash escaping the apostrophe. ;-)
>> This would be because you're not using mysql_real_escape_string() on the
>> third parameter. Try this (not tested):
>>
>> $sql_insert_registration = sprintf("INSERT INTO
>> Registrations (
>> Class_ID,
>> prid,
>> Registrant,
>> Company,
>> Phone,
>> Email
>> )
>> VALUES (%s, %s, '%s', '%s', '%s', '%s')",
>> $_POST[Class_ID],
>> $_POST[prid],
>> mysql_real_escape_string(parseNull($_POST['Company'])),
>> mysql_real_escape_string($_POST[Phone]),
>> mysql_real_escape_string($_POST[Email]),
>> mysql_real_escape_string($_POST['Registrant']));
>>
>>
>> --
>> Niel Archer
>> niel.archer (at) blueyonder.co.uk
To reduce the amount of repetitive called to mysql_real_escape_string(), create a method/function to
do the work for you....
<?php
function escape ($item) {
if (is_array ($item)) {
foreach ($item as $field => $value) {
$escaped[$field] = escape ($value);
}
}
else {
$escaped = mysql_real_escape_string ($item);
}
return $escaped;
}
$_POST['Company'] = parseNull ($_POST['Company']);
$p = escape ($_POST);
$sql = "INSERT INTO Registrations (Class_ID, prid, Registrant, Company, Phone, Email) VALUES
('{$p['Class_ID']}', '{$p['prid']}',
'{$p['Registrant']}', '{$p['Company']}',
'{$p['Phone']}', '{$p['Email']}')";
?>
Don't know if that helps any, but it may take some of the monotony out of it.
Cheers,
~Philip
http://lonestarlightandsound.com/