keep getting a syntax error (php / mysql)

Question

php/mysql

I keep getting this error: "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 '1' at line 1".

I'm trying hard to make this query to happen. It works, it inserts into the mysql database but this error appears every time. I've tried to use everything in the same line, changed double quotes to single quotes, removed all the whitespaces inserting everything in the samen line, changing the way I pass the variables({$variable} to '.$variable.') and everything else. I've seen a couple of stackoverflow questions related to this but with different solutions. I know that we can't pass '' in a numeric fields.

I think I'm out of options now. Need help! This error keeps showing but the data is correctly inserted in my table

here is the code:

$user_id = get_current_user_id();
$prescription_name = $_POST['prescription_name'];
$date_created = date('Y-m-d');
$last_updated = date('Y-m-d');
$right_eye_sphere = $_POST['right_eye_sphere'];
$left_eye_sphere = $_POST['left_eye_sphere'];
$right_eye_cylinder = $_POST['right_eye_cylinder'];
$left_eye_cylinder = $_POST['left_eye_cylinder'];
$right_eye_axis = $_POST['right_eye_axis'];
$left_eye_axis = $_POST['left_eye_axis'];
$pd = $_POST['pd'];
$date_of_birth = $_POST['date_of_birth'];
$file_path = $_POST['file_path'];

$add_query = "INSERT INTO wew_prescription (
        prescription_id,
        user_id,
        prescription_name,
        date_created,
        last_updated,
        right_eye_sphere,
        left_eye_sphere,
        right_eye_cylinder,
        left_eye_cylinder,
        right_eye_axis,
        left_eye_axis,
        pd,
        date_of_birth,
        file_path
        ) Values (
        NULL,
        {$user_id},
        '{$prescription_name}',
        '{$date_created}',
        '{$last_updated}',
        '{$right_eye_sphere}',
        '{$left_eye_sphere}',
        '{$right_eye_cylinder}',
        '{$left_eye_cylinder}',
        '{$right_eye_axis}',
        '{$left_eye_axis}',
        '{$pd}',
        '{$date_of_birth}',
        '{$file_path}'
        )";

    $sql = $dbCon->query($add_query);

    if (!mysqli_query($dbCon,$sql)){
        die('Error: ' . mysqli_error($dbCon));
    }else{
        mysqli_query($dbCon,$sql);
        echo "dados atualizados!";
    }

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, Mar 24 '17 at 02:11
It's unusual to mix object oriented style function calls `$con->query($sql)` and procedure function calls `mysqli_query($con,$sql)`. It's also a bit odd to to assign the result from a query execution to a variable named `$sql`. It's just a variable name, it can be whatever. But if I was going to assign something to a variable named `$sql`, it would be the SQL text, the string value being assigned to `$add_query`. As Barmar says, the code is executing the string `"1"`. — spencer7593, Mar 24 '17 at 02:19
thanks guys! I'll look into the prepared parameterised statements! I'm a front end developer learning mysql — Fausto Filho, Mar 24 '17 at 02:35

score 2 · Accepted Answer · answered Mar 24 '17 at 02:04

2

The error is coming from this line:

if (!mysqli_query($dbCon,$sql)){

$sql contains the result of

$dbCon->query($add_query);

Since that query was successful, $sql contains TRUE. mysqli_query() requires the second argument to be a string, so TRUE becomes "1", so you're effectively doing:

if (!mysqli_query($dbCon, "1")) {

That's not a valid query, so you get an error.

I think what you really meant to do was:

if (!$sql) {
    die('Error: ' . $dbCon->error);
} else {
    echo "dados atualizados!";
}

You don't need to keep calling mysqli_query() repeatedly.

You should also learn to code using prepared statements instead of substituting variables into the query, to prevent SQL injection.

answered Mar 24 '17 at 02:04

Barmar

741,623
53
500
612

Thank you Barmar! I was stuck, thinking on the query not on the if below! – Fausto Filho Mar 24 '17 at 02:21
I'd fix the code a little differently, just changing the one line that does the assignment to `$sql`... to make it read "**`$sql = $add_query;`**". – spencer7593 Mar 24 '17 at 02:23
That would be wrong, because then it will call `mysqli_query()` twice in the `if`. – Barmar Mar 24 '17 at 02:25

keep getting a syntax error (php / mysql)

1 Answers1

Linked

Related