Stuck in apostrophe hell

Stuck in apostrophe hell

am 02.08.2010 22:30:06 von Paul_S_Johnson

--=_alternative 0070606C86257773_=
Content-Type: text/plain; charset="US-ASCII"

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 "

".$_POST["Registrant"]."
";
echo "
".mysql_real_escape_string($_POST["Registrant"])."
";
echo "
".$sql_insert_registration."
";

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

--=_alternative 0070606C86257773_=--

Re: Stuck in apostrophe hell

am 02.08.2010 22:36:10 von Peter Lind

On 2 August 2010 22:30, 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 =3D 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 "

".$_POST["Registrant"]."
";
> echo "
".mysql_real_escape_string($_POST["Registrant"])."
";
> echo "
".$sql_insert_registration."
";
>
> if (!mysql_query($sql_insert_registration, $con)) {
>  die('Error: ' . mysql_error());
> ....
> ==================== =====
==============
>
>
> Here's the output:
> ==================== ===3D
>
> 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
> ==================== =====
==================== =====3D=
=3D

It's probably nothing but your mysql error does not match your php
output - could you try an updated paste?

Regards
Peter

--=20

WWW: http://plphp.dk / http://plind.dk
LinkedIn: http://www.linkedin.com/in/plind
BeWelcome/Couchsurfing: Fake51
Twitter: http://twitter.com/kafe15


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Stuck in apostrophe hell

am 02.08.2010 23:33:32 von Niel Archer

> 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 "

".$_POST["Registrant"]."
";
> echo "
".mysql_real_escape_string($_POST["Registrant"])."
";
> echo "
".$sql_insert_registration."
";
>
> 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



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Stuck in apostrophe hell

am 02.08.2010 23:42:59 von Bret Hughes

--------------030005040105080005050004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 "

".$_POST["Registrant"]."
";
>> echo "
".mysql_real_escape_string($_POST["Registrant"])."
";
>> echo "
".$sql_insert_registration."
";
>>
>> 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
>
>
>
>

--------------030005040105080005050004--

Re: Stuck in apostrophe hell

am 03.08.2010 03:46:48 von Philip Thompson

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.
>=20
> 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:
>>>=20
>>> Here's the relevant PHP code:
>>> ==================== =====
==============
>>> $sql_insert_registration =3D 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']));
>>>=20
>>> echo "

".$_POST["Registrant"]."
";
>>> echo =
"
".mysql_real_escape_string($_POST["Registrant"])."
";
>>> echo "
".$sql_insert_registration."
";
>>>=20
>>> if (!mysql_query($sql_insert_registration, $con)) { die('Error: ' =
.. mysql_error()); ....
>>> ==================== =====
==============
>>>=20
>>>=20
>>> Here's the output:
>>> ==================== ===3D
>>>=20
>>> 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
>>> ==================== =====
==================== =====3D=
=3D
>>>=20
>>>=20
>>> 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:
>>>=20
>>> ==================== =====
===========3D
>>>=20
>>> 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.
>>> ==================== =====
=========3D
>>>=20
>>> Very strange.
>>>=20
>>> I've checked various PHP variables and cannot figure out. It works =
fines from another PHP server that's using the same MySQL database.
>>>=20
>>> Thanks,
>>>=20
>>> Paul
>>> =20
>>=20
>> 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):
>>=20
>> $sql_insert_registration =3D 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']));
>>=20
>>=20
>> --
>> 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....

function escape ($item) {
if (is_array ($item)) {
foreach ($item as $field =3D> $value) {
$escaped[$field] =3D escape ($value);
}
}
else {
$escaped =3D mysql_real_escape_string ($item);
}
=20
return $escaped;
}

$_POST['Company'] =3D parseNull ($_POST['Company']);
$p =3D escape ($_POST);

$sql =3D "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

Re: Stuck in apostrophe hell

am 03.08.2010 15:04:19 von Paul_S_Johnson

--=_alternative 0047902486257774_=
Content-Type: text/plain; charset="US-ASCII"

Yes, I may have mixed up the input and output from different iterations of
running it. Let me try posting this again although it may not be an issue.
Once again if I enter two sequential apostrophes in the name (O''Brien)
the INSERT passes right through to MySQL without an error.

THE INPUT:

$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 "

$_POST['Registrant".$_POST["Registrant"]."
";
echo "
".mysql_real_escape_string($_POST["Registrant"])."
";
echo "
".$sql_insert_registration."
";


THE OUTPUT:

Brian O'Brien
Brian O\'Brien
INSERT INTO
Registrations (
Class_ID,
prid,
Registrant,
Company,
Phone,
Email
)
VALUES (
355,
257,
'Brian O\'Brien',NULL,
'612-456-5678',
'somebody@somewhere.org'
)
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


Paul S. Johnson
U.S. Bankruptcy Court
District of Minnesota
paul_s_johnson@mnb.uscourts.gov
612-664-5276
--=_alternative 0047902486257774_=--

Re: Re: Stuck in apostrophe hell

am 03.08.2010 15:08:47 von Peter Lind

On 3 August 2010 15:04, wrote:
> Yes, I may have mixed up the input and output from different iterations o=
f
> running it. Let me try posting this again although it may not be an issue=
..
> Once again if I enter two sequential apostrophes in the name (O''Brien)
> the INSERT passes right through to MySQL without an error.
>
> THE INPUT:
>
> $sql_insert_registration =3D 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 "

$_POST['Registrant".$_POST["Registrant"]."
";
> echo "
".mysql_real_escape_string($_POST["Registrant"])."
";
> echo "
".$sql_insert_registration."
";
>
>
> THE OUTPUT:
>
> Brian O'Brien
> Brian O\'Brien
> INSERT INTO
>  Registrations (
>    Class_ID,
>    prid,
>    Registrant,
>    Company,
>    Phone,
>    Email
>  )
> VALUES (
>    355,
>    257,
>    'Brian O\'Brien',NULL,
>    '612-456-5678',
>    'somebody@somewhere.org'
> )
> 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
>

Strangely, you have still failed to provide the input that is actually
sent to mysql. Look at the error code: "... for the right syntax to
use near 'Brien', 'Class registration confirmation', ' This email '" -
"Class registration confirmation" does not appear anywhere in the
output section you posted but it appears in the mysql error.
I'd do as Bret suggested and turn on query logging in mysql to see
what is actually received.

Regards
Peter

--=20

WWW: http://plphp.dk / http://plind.dk
LinkedIn: http://www.linkedin.com/in/plind
BeWelcome/Couchsurfing: Fake51
Twitter: http://twitter.com/kafe15


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Stuck in apostrophe hell

am 04.08.2010 00:44:44 von dmagick

On 03/08/10 23:04, Paul_S_Johnson@mnb.uscourts.gov wrote:
> Yes, I may have mixed up the input and output from different iterations of
> running it. Let me try posting this again although it may not be an issue.
> Once again if I enter two sequential apostrophes in the name (O''Brien)
> the INSERT passes right through to MySQL without an error.
>
> THE INPUT:
>
> $sql_insert_registration = sprintf("INSERT INTO
> Registrations (
> Class_ID,
> prid,
> Registrant,
> Company,
> Phone,
> Email
> )
> VALUES (
> $_POST[Class_ID],

^^^^^^ needs a mysql_real_escape_string or validation to make sure it's
an integer

> $_POST[prid],

^^^^^^ needs a mysql_real_escape_string or validation to make sure it's
an integer

> '%s',".

^^^^^^ has a mysql_real_escape_string, but it's the only one.

> parseNull($_POST['Company']).",

Without knowing what this function does, it's hard to say what this needs.

> '$_POST[Phone]',

^^^^^^ needs a mysql_real_escape_string

> '$_POST[Email]'

^^^^^^ needs a mysql_real_escape_string

> )", mysql_real_escape_string($_POST['Registrant']));


This has already all been pointed out previously.


--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Stuck in apostrophe hell

am 04.08.2010 02:24:52 von Karl DeSaulniers

On Aug 3, 2010, at 8:08 AM, Peter Lind wrote:

> On 3 August 2010 15:04, wrote:
>> Yes, I may have mixed up the input and output from different
>> iterations of
>> running it. Let me try posting this again although it may not be
>> an issue.
>> Once again if I enter two sequential apostrophes in the name
>> (O''Brien)
>> the INSERT passes right through to MySQL without an error.
>>
>> THE INPUT:
>>
>> $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 "

$_POST['Registrant".$_POST["Registrant"]."
";
>> echo "
".mysql_real_escape_string($_POST["Registrant"])."
";
>> echo "
".$sql_insert_registration."
";
>>
>>
>> THE OUTPUT:
>>
>> Brian O'Brien
>> Brian O\'Brien
>> INSERT INTO
>> Registrations (
>> Class_ID,
>> prid,
>> Registrant,
>> Company,
>> Phone,
>> Email
>> )
>> VALUES (
>> 355,
>> 257,
>> 'Brian O\'Brien',NULL,
>> '612-456-5678',
>> 'somebody@somewhere.org'
>> )
>> 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
>>
>
> Strangely, you have still failed to provide the input that is actually
> sent to mysql. Look at the error code: "... for the right syntax to
> use near 'Brien', 'Class registration confirmation', ' This email '" -
> "Class registration confirmation" does not appear anywhere in the
> output section you posted but it appears in the mysql error.
> I'd do as Bret suggested and turn on query logging in mysql to see
> what is actually received.
>
> Regards
> Peter
>
> --
>
> WWW: http://plphp.dk / http://plind.dk
> LinkedIn: http://www.linkedin.com/in/plind
> BeWelcome/Couchsurfing: Fake51
> Twitter: http://twitter.com/kafe15
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
Can't you just..

VALUES = mysql_real_escape(VALUES);
before submitting
or something similar?

maybe urlencode

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Stuck in apostrophe hell

am 04.08.2010 02:31:55 von Karl DeSaulniers

On Aug 3, 2010, at 5:44 PM, Chris wrote:

> On 03/08/10 23:04, Paul_S_Johnson@mnb.uscourts.gov wrote:
>> Yes, I may have mixed up the input and output from different
>> iterations of
>> running it. Let me try posting this again although it may not be
>> an issue.
>> Once again if I enter two sequential apostrophes in the name
>> (O''Brien)
>> the INSERT passes right through to MySQL without an error.
>>
>> THE INPUT:
>>
>> $sql_insert_registration = sprintf("INSERT INTO
>> Registrations (
>> Class_ID,
>> prid,
>> Registrant,
>> Company,
>> Phone,
>> Email
>> )
>> VALUES (
>> $_POST[Class_ID],
>
> ^^^^^^ needs a mysql_real_escape_string or validation to make sure
> it's an integer
>
>> $_POST[prid],
>
> ^^^^^^ needs a mysql_real_escape_string or validation to make sure
> it's an integer
>
>> '%s',".
>
> ^^^^^^ has a mysql_real_escape_string, but it's the only one.
>
>> parseNull($_POST['Company']).",
>
> Without knowing what this function does, it's hard to say what this
> needs.
>
>> '$_POST[Phone]',
>
> ^^^^^^ needs a mysql_real_escape_string
>
>> '$_POST[Email]'
>
> ^^^^^^ needs a mysql_real_escape_string
>
>> )", mysql_real_escape_string($_POST['Registrant']));
>
>
> This has already all been pointed out previously.
>
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


didnt see this post, was in my spam folder

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Stuck in apostrophe hell

am 04.08.2010 03:24:50 von David Robley

Paul_S_Johnson@mnb.uscourts.gov wrote:

> Yes, I may have mixed up the input and output from different iterations of
> running it. Let me try posting this again although it may not be an issue.
> Once again if I enter two sequential apostrophes in the name (O''Brien)
> the INSERT passes right through to MySQL without an error.
>
> THE INPUT:
>
> $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 "

$_POST['Registrant".$_POST["Registrant"]."
";
> echo "
".mysql_real_escape_string($_POST["Registrant"])."
";
> echo "
".$sql_insert_registration."
";
>
>
> THE OUTPUT:
>
> Brian O'Brien
> Brian O\'Brien
> INSERT INTO
> Registrations (
> Class_ID,
> prid,
> Registrant,
> Company,
> Phone,
> Email
> )
> VALUES (
> 355,
> 257,
> 'Brian O\'Brien',NULL,
> '612-456-5678',
> 'somebody@somewhere.org'
> )
> 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
>
>
> Paul S. Johnson
> U.S. Bankruptcy Court
> District of Minnesota
> paul_s_johnson@mnb.uscourts.gov
> 612-664-5276

Check the settings for magic-quotes, and make sure you aren't using
stripslashes somewhere?

Also, echo the actual query that is being passed to mysql to check what is
happening.


Cheers
--
David Robley

Life is Roff when yer Stewpid
Today is Sweetmorn, the 70th day of Confusion in the YOLD 3176.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Stuck in apostrophe hell

am 04.08.2010 09:19:24 von Simcha

> Paul_S_Johnson@mnb.uscourts.gov wrote:

> >
> > THE INPUT:
> >
> > $sql_insert_registration = sprintf("INSERT INTO
> > Registrations (
> > Class_ID,
> > prid,
> > Registrant,
> > Company,
> > Phone,
> > Email
> > )
> > VALUES (
> > $_POST[Class_ID],
> > $_POST[prid],
> > '%s',".

You need double-quotes here,
\"%s\",

> > parseNull($_POST['Company']).",
> > '$_POST[Phone]',
> > '$_POST[Email]'
> > )", mysql_real_escape_string($_POST['Registrant']));
> >


--
Simcha Younger

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Re: Stuck in apostrophe hell

am 04.08.2010 10:20:28 von M.Ford

> -----Original Message-----
> From: Simcha Younger [mailto:simcha@syounger.com]
> Sent: 04 August 2010 08:19
>=20
> > Paul_S_Johnson@mnb.uscourts.gov wrote:
>=20
> > >
> > > THE INPUT:
> > >
> > > $sql_insert_registration =3D sprintf("INSERT INTO
> > > Registrations (
> > > Class_ID,
> > > prid,
> > > Registrant,
> > > Company,
> > > Phone,
> > > Email
> > > )
> > > VALUES (
> > > $_POST[Class_ID],
> > > $_POST[prid],
> > > '%s',".
>=20
> You need double-quotes here,
> \"%s\",

No, he doesn't. Single quotes are fine. Doubles would more than likely be a=
SQL error.

> > > parseNull($_POST['Company']).",
> > > '$_POST[Phone]',
> > > '$_POST[Email]'
> > > )", mysql_real_escape_string($_POST['Registrant']));
> > >
>=20
>=20
> --
> Simcha Younger


Cheers!

Mike

--=20
Mike Ford,
Electronic Information Developer, Libraries and Learning Innovation,
Leeds Metropolitan University, C507 City Campus,=20
Woodhouse Lane, LEEDS,=A0 LS1 3HE,=A0 United Kingdom=20
Email: m.ford@leedsmet.ac.uk=20
Tel: +44 113 812 4730




To view the terms under which this email is distributed, please go to http:=
//disclaimer.leedsmet.ac.uk/email.htm

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Stuck in apostrophe hell

am 04.08.2010 16:16:40 von Paul_S_Johnson

--=_alternative 004E2FCC86257775_=
Content-Type: text/plain; charset="US-ASCII"

OK, I figured it out. I followed the advice here to turn on MySQL logging
(which took more doing that it should have), so I could see what's really
being sent to MySQL. It wasn't choking on the query I posted in my message
but a later one in which the string was not escaped. The red herring that
led me astray was the line no. indicated in the error message that pointed
to the query I posted (or at least seemed to).

Anyway, thanks for the tips that got me pointed in the right direction.

Paul

Paul S. Johnson
--=_alternative 004E2FCC86257775_=--