UPDATE Query in PHP/MySQL Failing with Error Code 1064, SQLState 42000

Question

When building my app, I made the create query with no trouble. However, when I copied my PHP from the create file to the update file, I've been getting this error:

UPDATE people SET firstname = 'First', lastname = 'Last', email = '[email protected]', phonenumber = 1234567890 WHERE id = 1'

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''' at line 1

Usually, when I get this error, the error gives me an accurate place to fix up. Can anyone help me find this error?

update.sql:

if (isset($_POST['submit'])) {
    require "../resources/config.php";
    require "../resources/common.php";

    try {
        $connection = new PDO($dsn, $username, $password, $options);

        $id = $_GET['id'];

        $firstname = $_POST['firstname'];
        $lastname = $_POST['lastname'];
        $email = $_POST['email'];
        $phonenumber = $_POST['phonenumber'];

        $updated_number = array($firstname, $lastname, $email, $phonenumber);

        $sql = sprintf(
            "UPDATE %s SET firstname = '$firstname', lastname = '$lastname', email = '$email', phonenumber = $phonenumber WHERE id = %s",
            "people",
            $id
        );

        $statement = $connection->prepare($sql);
        $statement->execute($updated_number);
        header("Location: index.php");
    } 

    catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}

try: put $phonenumber in singlequotes in the query. without it is a number — Bernd Buffen
– Bernd Buffen, Commented Apr 8, 2018 at 14:42

chris85 · Accepted Answer · 2018-04-08 15:11:42Z

1

You have two issues here. The first, and more important is your use of prepared statements. All values in the query itself should be bound. So your query should really be:

$updated_number = array($firstname, $lastname, $email, $phonenumber, $id);
$sql = sprintf("UPDATE %s 
                SET firstname = ?, lastname = ?, email = ?, phonenumber = ? 
                WHERE id = ?",
            "people");

The second is your sprintf usage.

WHERE id = %s

The %s is a string, %d is for an integer. With correct prepared statements this isn't needed though. If "people" isn't a variable and being built dynamically I think it would be easier to just build that whole query as a normal string. e.g.

$sql = 'UPDATE people
        SET firstname = ?, lastname = ?, email = ?, phonenumber = ? 
        WHERE id = ?';

answered Apr 8, 2018 at 15:11

chris85

23.9k7 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ThatGuyInIT · Accepted Answer · 2018-04-08 15:15:02Z

1

Don't use sprintf for building SQL statements as it opens your code up to SQL Injection attacks, it is better to use prepared statements which would looks something like this:

    $sql = "UPDATE `people` SET `firstname` = :firstname, `lastname` = :lastname, `email` = :email, `phonenumber` = :phonenumber WHERE `id` = :id;"

    $statement = $connection->prepare($sql);
    $statement->bindParam(':firstname', $firstname);
    $statement->bindParam(':lastname', $lastname);
    $statement->bindParam(':email', $email);
    $statement->bindParam(':phonenumber', $phonenumber);
    $statement->bindParam(':id', $id);

answered Apr 8, 2018 at 15:15

ThatGuyInIT

2,19517 silver badges20 bronze badges

1 Comment

Nerdi.org Over a year ago

If you're going to learn prepared statements, here's a useful tool for it. wbr.bz/QueryPro

Collectives™ on Stack Overflow

UPDATE Query in PHP/MySQL Failing with Error Code 1064, SQLState 42000

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related