Database error. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax

Question

I am getting this error:

An error occured: Database error. 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 '@gmail.com, salt = , iteration = 12, method = blowfish, person_id' at line 1

Here is the code:

$data_con = new data_abstraction;
$data_con->execute_query("INSERT into `USER` SET `username` = $user, `password` = $phsh, `email` = $email, `salt` = $salt, `iteration` = $new_iteration, `method` = $new_method, `person_id` = $result2, `role_id` = $result4, `skin_id` = $result5");

Edit:

I already used prepared the query, used parameterized statements and bind the parameters. The error has gone but the details I want to be inserted into the table are not added.

Here is my code:

if(!($sql = $link->prepare("INSERT into `USER` SET 
                                    `username`  = ?, 
                                    `password`  = ?, 
                                    `email`     = ?, 
                                    `salt`      = ?, 
                                    `iteration` = ?, 
                                    `method`    = ?, 
                                    `person_id` = ?, 
                                    `role_id`   = ?, 
                                    `skin_id`   = ?"))){
                                        echo "SQL Query Preparation has failed";
                                    }

                    if(!($sql->bind_param("ssssisiii", $user, $phsh, $email, $new_salt, $new_iteration, $new_method, $result2, $result4, $result5))){
                        echo "Parameter Binding failed";
                    }

                    if(!($sql->execute())){
                        echo "MySQL Query Execution has failed";
                    }

INSERT INTO USER (username,password) values($user,$password); — RJParikh, Oct 26 '16 at 09:54
@RiggsFolly i am sorry i think that its a insert. but he mixing :) — Karthi, Oct 26 '16 at 09:59
Possible duplicate of [#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version](http://stackoverflow.com/questions/24324636/1064-you-have-an-error-in-your-sql-syntax-check-the-manual-that-corresponds) — SnakeEye, Oct 26 '16 at 10:09
@LifeTimeProgrammer No my mistake, I always forget you can use SET in a INSERT — RiggsFolly, Oct 26 '16 at 10:11

score 1 · Answer 1 · edited May 23 '17 at 10:29

All text data must be wrapped in quotes in a concatenated query like this. It is also legal to wrap integers in quotes so its safer to do both like this

$data_con = new data_abstraction;
$data_con->execute_query("INSERT into `USER` SET 
            `username`  = '$user', 
            `password`  = '$phsh', 
            `email`     = '$email', 
            `salt`      = '$salt', 
            `iteration` = '$new_iteration', 
            `method`    = '$new_method', 
            `person_id` = '$result2', 
            `role_id`   = '$result4', 
            `skin_id`   = '$result5'");

It would be better to use a parameterised query like this and then bind the values

$sql = "INSERT into `USER` SET 
                `username`  = ?, 
                `password`  = ?, 
                `email`     = ?, 
                `salt`      = ?, 
                `iteration` = ?, 
                `method`    = ?, 
                `person_id` = ?, 
                `role_id`   = ?, 
                `skin_id`   = ?");

And then prepare the query and bind the values to the ? using bind_param

This will also protect you from SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements

what is the use for insert query with `SET ` and without `SET`? shot ans sweet and +1 — Karthi, Oct 26 '16 at 10:18
@LifeTimeProgrammer Sorry cannot answer that, its legal syntax, but I have never used it myself, which is why it always trips me up when I see it and my first though is always that it is incorrect syntax — RiggsFolly, Oct 26 '16 at 10:23

Muhammed Imran Hussain · Answer 2 · 2016-10-26T09:55:55.957

-1

$email contains special character so quote email column value email = '$email'

edited Oct 26 '16 at 09:55

answered Oct 26 '16 at 09:54

Muhammed Imran Hussain

2,057
16
24

That is true, but the OP is also using some sort of database handler, so it might fix that for him. Since I'm not familiar with execute_query() and the data_abstraction class, I have no idea what that does (but yes, I also missed the obvious error). – junkfoodjunkie Oct 26 '16 at 09:57
@RiggsFolly are you sure? did you checked it or just commented and given minus rating in my comment? – Muhammed Imran Hussain Oct 26 '16 at 09:58
@junkfoodjunkie are you sure? did you checked it or just commented and given minus rating in my comment? – Muhammed Imran Hussain Oct 26 '16 at 10:00
Sorry, always forget you can use SET in an INSERT, it gets me every time. Brain hiccup – RiggsFolly Oct 26 '16 at 10:00

score -1 · Answer 3 · answered Oct 26 '16 at 09:55

You need to wrap each $variable in single quotes ''. BTW, that is horrible code, unless the execute_query() method sanitizes those input variables. If it doesn't, you seriously need to take a look at parameterizing your queries. I'm assuming you use mysqli? If so, take a look here: http://php.net/manual/en/mysqli.prepare.php

Database error. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax

3 Answers3