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, Dec 28 '20 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, Dec 28 '20 at 19:46
Im only get the Syntax-Error but i checked everything and i cant find any syntax error — Niggo, Dec 28 '20 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, Dec 28 '20 at 19:57

miken32 · Accepted Answer · 2020-12-28T20:00:49.243

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 '20 at 20:00

answered Dec 28 '20 at 19:52

miken32

42,008
16
111
154

yes, but i've done it in the other way like hundreds of times already why does it fail so suddenly? but anyway thanks – Niggo Dec 28 '20 at 19:53
1

Well don't do it the other way. It's unsafe and guaranteed to break. – miken32 Dec 28 '20 at 19:54
1

Also, if the first column of `ratings` is an auto-increment, that empty string should more properly be a `NULL`. – miken32 Dec 28 '20 at 19:56

MYSQL Insert Statement not working in code but working in phpmyadmin

1 Answers1