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',
>>> '[email protected]'
>>> )
>>> 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',
>>> '[email protected]'
>>> )
>>> 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/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
