奇怪的未解决的PDO错误：语法错误或访问冲突：1064您的SQL语法中有错误;

Question

I have got an error in a php script:

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 MySQL server version for the right syntax to use near '' at line 1 in /var/www/html/a.php:143

Pay attention it says near ''(two single quotations). This script used to work correctly on my previous server but when I moved to the new server, it kept showing this error. My PHP version is 7.0 and Mysql version is 5.7.22.

How I connected to mysql:

try{
    $pdo = new PDO("mysql:host=localhost;dbname=db_name", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch(PDOException $e){
    echo "Connection failed: " . $e->getMessage();
}

It prints: "Connected successfully". These are lines 142 and 143 which contain PDO queries:

$stmt = $pdo->prepare("SELECT has_paid FROM info WHERE chat_id=".$chat_id);
$stmt->execute();

I visited similar questions but they didn't help.

How can I solve this?

Answer 1

I suspect your $chat_id is empty. So your SQL query ends up being:

SELECT has_paid FROM info WHERE chat_id=

MySQL syntax errors report the part of the SQL query that follows the place where MySQL got confused. In this case, it reports an error near '' because it got confused at the end of the query. This is not surprising, because = requires a right-hand-side argument.

You should examine the code where you set $chat_id and make sure it has a non-empty value before attempting your query.

By the way, it seems strange to me that you're using prepare() and execute() but you're not using query parameters. You should code it this way:

$stmt = $pdo->prepare("SELECT has_paid FROM info WHERE chat_id=?");
$stmt->execute([$chat_id]);

(There is no need to use bindValue() as some people will say.)