Posted by mmckeon@gmail.com on 10/25/06 12:41

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

[Back to original message]