1

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.

Balaji Kandasamy
  • 4,446
  • 10
  • 40
  • 58
Mert
  • 11
  • 1
  • 2
  • (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

2 Answers2

3

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

Edit 2023: If there is a table called users its a much better and cleaner way to define column names like id, name, age, etc... instead of user_id, user_name, or user_age

Halid Kyazim
  • 43
  • 1
  • 8
0

You can also try like this to execute the query using prepare() and execute() methods.

$stmt = $conn->prepare("SELECT * FROM demo WHERE user=:user and dripfeed=:dripfeed");
$stmt->execute([':user'=>$user_id,':dripfeed'=>2]);
$dripfeedcount = $stmt->rowCount();
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Balaji Kandasamy
  • 4,446
  • 10
  • 40
  • 58