How to fix MySQL error: You have an error in your SQL syntax

Question

Below code doesn't work:

$connection = mysqli_connect("XXX", "XXX", "XXX", "XXX");
if (isset($_POST['answer'])) {
    if (!mysqli_query($connection, "INSERT INTO `answers`(`questionId`, `dateAndTime`, `answer`) VALUES ({$_SESSION['id']},(SELECT now()),{$_POST['answer']})")) {
        echo ("Error description: " . mysqli_error($connection));
    }
}

Output: Error description: 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

Could you tell me what is wrong?

Learn to use parameters. That usually fixes these types of problems. — Gordon Linoff, Sep 04 '21 at 12:14
yup if $_POST['answer'] is a string, then its going to break the query, why are you SELECT now() and not just now()? — Lawrence Cherone, Sep 04 '21 at 12:24
Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should alway use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! — RiggsFolly, Sep 04 '21 at 12:31

score -2 · Answer 1 · answered Sep 04 '21 at 12:36

-2

The best way is to use PDO connexion like this :

$host= "your host"; //for example 127.0.0.1 or localhost
$db_name = "your dbname";
$user = "your username";
$password = "your password";

$bdd = new PDO("mysql:host=" . $host . ";dbname=" . $db_name , $user , $password);

if(isset($_POST['answer'])) 
{
   $createdAt = date('Y-m-d h:i:sa');

   $insert = $bdd->prepare("INSERT INTO answers(questionId, dateAndTime, answer) VALUES(?, ?, ?)");
   $insert->execute(array($_SESSION['id'], $createdAt, $_POST['answer'])) or die (print_r($insert->errorInfo())); 
}

answered Sep 04 '21 at 12:36

Horly Andelo

51
7

1

please look up how to prevent sql injection https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk Sep 04 '21 at 12:40
or die (print_r($insert->errorInfo())); Will allow you to display syntax errors – Horly Andelo Sep 04 '21 at 12:43
se thge problemthat he has is that in the variables can be character that will make the string not useble for mysql, so prepared statements help woth taht and **sql injection** – nbk Sep 04 '21 at 13:11

How to fix MySQL error: You have an error in your SQL syntax

1 Answers1