By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,727 Members | 1,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,727 IT Pros & Developers. It's quick & easy.

strange extra quote mark appearing in mysql query

P: n/a
(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
'x***@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.

Oct 24 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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
'x***@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.
js*******@attglobal.net
==================
Oct 24 '06 #2

P: n/a
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
'x***@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.
js*******@attglobal.net
==================
Oct 24 '06 #3

P: n/a
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
'x***@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

Oct 24 '06 #4

P: n/a
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
Oct 24 '06 #5

P: n/a
Rik
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
'x***@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
Oct 24 '06 #6

P: n/a
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.

Oct 24 '06 #7

P: n/a
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.

Oct 24 '06 #8

P: n/a
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.

Oct 24 '06 #9

P: n/a
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_string($usr) . "',
password = '" . mysql_escape_string($pwd} . "',
fullname = '" . mysql_escape_string($_POST['fullname'] . '",
email = '" . mysql_escape_string(_POST['email'] . "'
WHERE userid = '" . mysql_escape_string($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
'x***@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
Oct 25 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.