MYSQL Insert Statement not working in code but working in phpmyadmin

Question

So I just wanted to insert a new record to database using by below query:

INSERT INTO ratings VALUES(\'\','".$userid."','".$rating."','".$itemid."')

I also tried it with parameters but that didn't work either.

This is the query printed to console:

INSERT INTO ratings VALUES('','13','3.5','5228')"

The Php Code is this:

$query = "INSERT INTO ratings VALUES(\'\','".$userid."','".$rating."','".$itemid."')";
echo '<script>console.log("'.$query.'");</script>';
DB::query($query);

And the function to execute the query:

public static function query($query, $params = array()) {
    $statement = self::connect()->prepare($query);
    $statement->execute($params);

    if (explode(' ', $query)[0] == 'SELECT') {
        $data = $statement->fetchAll();
        return $data;
    }
}

And the Error that i get is following:

Uncaught PDOException: 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 '\'\','13','4','5228')'
at line 1 in D:\xampp\htdocs\updatedcollector\classes\DB.php:12

This is missing a lot of information. What was the php code? What's the error output? — mconkin
– mconkin, Commented Dec 28, 2020 at 19:46
Are you getting any errors? Try error_reporting(E_ALL); (place that on the top of your PHP script) and check your MySQL error logs. Also, show more code. — John Smith
– John Smith, Commented Dec 28, 2020 at 19:46
Im only get the Syntax-Error but i checked everything and i cant find any syntax error — Niggo
– Niggo, Commented Dec 28, 2020 at 19:50
You're in a double-quoted string, so you don't need to escape your single quotes. If you echo your query, you'll probably see the slashes still there. — aynber
– aynber, Commented Dec 28, 2020 at 19:57

miken32 · Accepted Answer · 2020-12-28 20:00:49Z

2

Given this function:

public static function query($query, $params = array()) {
    $statement = self::connect()->prepare($query);
    $statement->execute($params);

    if (explode(' ', $query)[0] == 'SELECT') {
        $data = $statement->fetchAll();
        return $data;
    }
}

It seems like what you want to do is this:

$query = "INSERT INTO ratings VALUES('', ?, ?, ?)";
DB::query($query, [$userid, $rating, $itemid]);

This provides you with protection against SQL injection attacks.

edited Dec 28, 2020 at 20:00

answered Dec 28, 2020 at 19:52

miken32

42.5k16 gold badges127 silver badges177 bronze badges

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

3 Comments

Niggo Over a year ago

yes, but i've done it in the other way like hundreds of times already why does it fail so suddenly? but anyway thanks

miken32 Over a year ago

Well don't do it the other way. It's unsafe and guaranteed to break.

miken32 Over a year ago

Also, if the first column of ratings is an auto-increment, that empty string should more properly be a NULL.

Collectives™ on Stack Overflow

MYSQL Insert Statement not working in code but working in phpmyadmin

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related