SQLSTATE[42000]: Syntax error or access violation: 1064 Error

Question

I am getting the following error on a website. I create ticket for this reason in my hosting provider. It told me "You need to edit the select query, not a select query suitable for the mariadb version on the server." they said.

error_log File:

 [25-Dec-2021 19:50:24 Europe] PHP Fatal error:  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 'and dripfeed= 2' at line 1 in /home/user/public_html/script.php:461
        Stack trace:
        #0 /home/user/public_html/script.php(461): PDO->query('SELECT * FROM o...')
        #1 /home/user/public_html/index.php(35): require_once('/home/user/...')
        #2 {main}
          thrown in /home/user/public_html/script.php on line 461

script.php File:

$dripfeedvarmi = $conn->query("SELECT * FROM demo WHERE user=$user_id and dripfeed=2");
        if ($dripfeedvarmi->rowCount())
        {
            $dripfeedcount = 1;
        }
        else
        {
            $dripfeedcount = 0;
        }

Current DB Version: 10.2.41-MariaDB-cll-lve
PHP Version: 7.4.25
OS: Linux

Thank you in advance for your help.

(Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Dec 26 '21 at 02:36

score 1 · Answer 1 · answered Dec 26 '21 at 03:12

even if the MySQL syntax is correct, do not write code like this. Always prepare your query to make it secure!

Try this example:

$query = 'SELECT * FROM demo WHERE user = ? AND dripfeed = ?';
$array = array($user_id, 2);

$init = $conn->prepare($query);
$init->execute($array);

$rowCount = $init->rowCount();

if($rowCount > 0){
    $dripfeedcount = 1;
}else{
    $dripfeedcount = 0;
};

Also if you are storing the id of the user, so why the column name is not user_id instead of user? Be clean...

SQLSTATE[42000]: Syntax error or access violation: 1064 Error

1 Answers1