Stuck in apostrophe hell
in [Databases]
|
Prev: downloading badges and putting it on any websites.
Next: PHP application hosted on a dektop ubuntu(localhost) vs A .NET software installed on Windows
From: Paul_S_Johnson on 2 Aug 2010 16:30 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, ) 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, ) VALUES ( 355, 257, 'Brian O\'Brien',NULL, '612-456-5678', 'paul_s_johnson(a)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, ) VALUES ( 355, 257, 'Brian O\'\'Brien',NULL, '612-456-5678', 'paul_s_johnson(a)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
From: Peter Lind on 2 Aug 2010 16:36 On 2 August 2010 22:30, <Paul_S_Johnson(a)mnb.uscourts.gov> 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(a)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 > ================================================== It's probably nothing but your mysql error does not match your php output - could you try an updated paste? Regards Peter -- <hype> WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 </hype>
From: Niel Archer on 2 Aug 2010 17:33 > 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, > ) > 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, > ) > VALUES ( > 355, > 257, > 'Brian O\'Brien',NULL, > '612-456-5678', > 'paul_s_johnson(a)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, > ) > VALUES ( > 355, > 257, > 'Brian O\'\'Brien',NULL, > '612-456-5678', > 'paul_s_johnson(a)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, ) 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
From: Bret Hughes on 2 Aug 2010 17:42 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, >> ) >> 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, >> ) >> VALUES ( >> 355, >> 257, >> 'Brian O\'Brien',NULL, >> '612-456-5678', >> 'paul_s_johnson(a)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, >> ) >> VALUES ( >> 355, >> 257, >> 'Brian O\'\'Brien',NULL, >> '612-456-5678', >> 'paul_s_johnson(a)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, > ) > 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 > > > >
From: Philip Thompson on 2 Aug 2010 21:46
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, >>> ) >>> 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, >>> ) >>> VALUES ( >>> 355, >>> 257, >>> 'Brian O\'Brien',NULL, >>> '612-456-5678', >>> 'paul_s_johnson(a)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, >>> ) >>> VALUES ( >>> 355, >>> 257, >>> 'Brian O\'\'Brien',NULL, >>> '612-456-5678', >>> 'paul_s_johnson(a)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, >> ) >> 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/ |