strange extra quote mark appearing in mysql query

[davek]

(posted to: php.general, comp.lang.php, alt.php, alt.php.sql)

I have a form where registered users on my site can edit their login
details. For some reason, the script is inserting an extraneous quote
mark in the mysql update query that is preventing it from running
successfully, but I am at a complete loss to understand why.

This is my code:
$sql = "UPDATE users SET
username = '{$usr}',
password = '{$pwd}',
fullname = '{$_POST['fullname']},
email = '{$_POST['email']}'
WHERE userid = '{$usrid}'";

if (@mysql_query($ sql)) {

//send email to user confirming changes

} else {

echo "<p>Error updating details: " . mysql_error() . "</p>";

}

This is the error message:
Error updating details: You have an error in your SQL syntax near
'xxxx@xxxx.com' WHERE userid = '15'' at line 4

I have checked that the $usrid variable does not contain the quote
mark.

Anyone have any bright ideas?

cheers,

d.

[Jerry Stuckle]

Re: strange extra quote mark appearing in mysql query

davek wrote:

(posted to: php.general, comp.lang.php, alt.php, alt.php.sql)
>
I have a form where registered users on my site can edit their login
details. For some reason, the script is inserting an extraneous quote
mark in the mysql update query that is preventing it from running
successfully, but I am at a complete loss to understand why.
>
This is my code:
$sql = "UPDATE users SET
username = '{$usr}',
password = '{$pwd}',
fullname = '{$_POST['fullname']},
email = '{$_POST['email']}'
WHERE userid = '{$usrid}'";
>
if (@mysql_query($ sql)) {
>
//send email to user confirming changes
>
} else {
>
echo "<p>Error updating details: " . mysql_error() . "</p>";
>
}
>
This is the error message:
Error updating details: You have an error in your SQL syntax near
'xxxx@xxxx.com' WHERE userid = '15'' at line 4
>
I have checked that the $usrid variable does not contain the quote
mark.
>
Anyone have any bright ideas?
>
cheers,
>
d.
>

`password` is a MySQL reserved word.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: strange extra quote mark appearing in mysql query

Jerry Stuckle wrote:

davek wrote:
>

>(posted to: php.general, comp.lang.php, alt.php, alt.php.sql)
>>
>I have a form where registered users on my site can edit their login
>details. For some reason, the script is inserting an extraneous quote
>mark in the mysql update query that is preventing it from running
>successfully , but I am at a complete loss to understand why.
>>
>This is my code:
>$sql = "UPDATE users SET
> username = '{$usr}',
> password = '{$pwd}',
> fullname = '{$_POST['fullname']},
> email = '{$_POST['email']}'
> WHERE userid = '{$usrid}'";
>>
>if (@mysql_query($ sql)) {
>>
>//send email to user confirming changes
>>
>} else {
>>
>echo "<p>Error updating details: " . mysql_error() . "</p>";
>>
>}
>>
>This is the error message:
>Error updating details: You have an error in your SQL syntax near
>'xxxx@xxxx.com ' WHERE userid = '15'' at line 4
>>
>I have checked that the $usrid variable does not contain the quote
>mark.
>>
>Anyone have any bright ideas?
>>
>cheers,
>>
>d.
>>

>
`password` is a MySQL reserved word.
>

I should also add:

fullname = '{$_POST['fullname']},

has mismatched quotes.


--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Robin]

Re: strange extra quote mark appearing in mysql query

Jerry Stuckle wrote:

Jerry Stuckle wrote:

>davek wrote:
>>

>>(posted to: php.general, comp.lang.php, alt.php, alt.php.sql)
>>>
>>I have a form where registered users on my site can edit their login
>>details. For some reason, the script is inserting an extraneous quote
>>mark in the mysql update query that is preventing it from running
>>successfull y, but I am at a complete loss to understand why.
>>>
>>This is my code:
>>$sql = "UPDATE users SET
>> username = '{$usr}',
>> password = '{$pwd}',
>> fullname = '{$_POST['fullname']},
>> email = '{$_POST['email']}'
>> WHERE userid = '{$usrid}'";
>>>
>>if (@mysql_query($ sql)) {
>>>
>>//send email to user confirming changes
>>>
>>} else {
>>>
>>echo "<p>Error updating details: " . mysql_error() . "</p>";
>>>
>>}
>>>
>>This is the error message:
>>Error updating details: You have an error in your SQL syntax near
>>'xxxx@xxxx.co m' WHERE userid = '15'' at line 4
>>>
>>I have checked that the $usrid variable does not contain the quote
>>mark.
>>>
>>Anyone have any bright ideas?
>>>
>>cheers,
>>>
>>d.
>>>

>>
>`password` is a MySQL reserved word.
>>

>
I should also add:
>
fullname = '{$_POST['fullname']},
>
has mismatched quotes.
>

And insecure without any validation.

Robin

[Markus Ernst]

Re: strange extra quote mark appearing in mysql query

davek schrieb:

This is my code:
$sql = "UPDATE users SET
username = '{$usr}',
password = '{$pwd}',
fullname = '{$_POST['fullname']},

End quote missing here ^

email = '{$_POST['email']}'
WHERE userid = '{$usrid}'";

You can easily find typos like this if you output var_dump($sql).

BTW it is a bad idea to put post data directly into your query - you
should check them for security issues and escape quotes first. Google
for "sql injection" and "e-mail injection".

--
Markus

[Rik]

Re: strange extra quote mark appearing in mysql query

davek wrote:

(posted to: php.general, comp.lang.php, alt.php, alt.php.sql)
>
I have a form where registered users on my site can edit their login
details. For some reason, the script is inserting an extraneous quote
mark in the mysql update query that is preventing it from running
successfully, but I am at a complete loss to understand why.
>
This is my code:
$sql = "UPDATE users SET
username = '{$usr}',
password = '{$pwd}',
fullname = '{$_POST['fullname']},
email = '{$_POST['email']}'
WHERE userid = '{$usrid}'";
>
if (@mysql_query($ sql)) {
>
//send email to user confirming changes
>
} else {
>
echo "<p>Error updating details: " . mysql_error() . "</p>";
>
}
>
This is the error message:
Error updating details: You have an error in your SQL syntax near
'xxxx@xxxx.com' WHERE userid = '15'' at line 4
>
I have checked that the $usrid variable does not contain the quote
mark.

As indicated earlier, you miss a quotation mark after fullname, but also:
- Try to always use backticks around fieldnames, it will save you a lot of
headache.
- In error messages like this, the error is 99% of the time on the left,
NOT the right.
- The extra quote is indeed not in your code, but the errormessage quotes a
part of your query, hence:
userid = '15'
becomes:
'userid = '15''
--
Grtz,

Rik Wasmus

[davek]

Re: strange extra quote mark appearing in mysql query

Markus Ernst wrote:

End quote missing here ^

doh! I missed that completely... thanks - just goes to show that
another pair of eyes is always useful...

BTW it is a bad idea to put post data directly into your query - you
should check them for security issues and escape quotes first. Google
for "sql injection" and "e-mail injection".

Thanks for the tip - I've seen sql injections mentioned elsewhere so
I'm vaguely aware of them and will get to grips with how to avoid them
before the site goes live... fortunately, it still exists only on my
testing server at the moment.

d.

[davek]

Re: strange extra quote mark appearing in mysql query

Jerry Stuckle wrote:

`password` is a MySQL reserved word.

Not something I've come across before, but just done a bit of
googling... thanks for the tip-off.

d.

[davek]

Re: strange extra quote mark appearing in mysql query

Rik wrote:

- Try to always use backticks around fieldnames, it will save you a lot of
headache.

That's to do with the mysql reserved names, right? Sounds like a
sensible plan.

userid = '15' becomes: 'userid = '15''

That makes sense. Thanks.

d.

[mmckeon@gmail.com]

Re: strange extra quote mark appearing in mysql query

At the very least you should be escaping your strings before
concatenate your string. What if someone's last name is D'Maro?? This
query will then fail to run. Preventing SQL injection isn't something
that you should go back and do, it should be a part of your query
writing process. At the very least your query should look like this:

$sql = "UPDATE users SET
username = '" . mysql_escape_st ring($usr) . "',
password = '" . mysql_escape_st ring($pwd} . "',
fullname = '" . mysql_escape_st ring($_POST['fullname'] . '",
email = '" . mysql_escape_st ring(_POST['email'] . "'
WHERE userid = '" . mysql_escape_st ring($usrid) . "'";


Robin wrote:

Jerry Stuckle wrote:

Jerry Stuckle wrote:

davek wrote:
>
>(posted to: php.general, comp.lang.php, alt.php, alt.php.sql)
>>
>I have a form where registered users on my site can edit their login
>details. For some reason, the script is inserting an extraneous quote
>mark in the mysql update query that is preventing it from running
>successfully , but I am at a complete loss to understand why.
>>
>This is my code:
>$sql = "UPDATE users SET
> username = '{$usr}',
> password = '{$pwd}',
> fullname = '{$_POST['fullname']},
> email = '{$_POST['email']}'
> WHERE userid = '{$usrid}'";
>>
>if (@mysql_query($ sql)) {
>>
>//send email to user confirming changes
>>
>} else {
>>
>echo "<p>Error updating details: " . mysql_error() . "</p>";
>>
>}
>>
>This is the error message:
>Error updating details: You have an error in your SQL syntax near
>'xxxx@xxxx.com ' WHERE userid = '15'' at line 4
>>
>I have checked that the $usrid variable does not contain the quote
>mark.
>>
>Anyone have any bright ideas?
>>
>cheers,
>>
>d.
>>
>
`password` is a MySQL reserved word.
>

I should also add:

fullname = '{$_POST['fullname']},

has mismatched quotes.

>
And insecure without any validation.
>
Robin