I need help with SQL syntax error.

Question

0.00/5 (No votes)

See more:

I have a php code that needs to update the data from the database, but it gives me this error: Error description: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 '' at line 1

This is my code:

<pre><?php
//initialize variables
$Pand =" ";
$Naam =" ";
$Email =" ";
$Huisnummer =" ";
$Deel =" ";
$id = 0;

// connect to database
$db = mysqli_connect("xxx","xxx","xxx","xxx");
//update records
if (isset($_POST['aanpassen'])) {
    $Naam = mysqli_real_escape_string($_POST["Naam"]);
    $Email = mysqli_real_escape_string($_POST["Email"]);
    $Pand = mysqli_real_escape_string($_POST["Pand"]);
    $Huisnummer = mysqli_real_escape_string($_POST["Huisnummer"]);
    $Deel = mysqli_real_escape_string($_POST["Deel"]);
    $id = mysqli_real_escape_string($_POST["id"]);
    
    if (!mysqli_query($db,"UPDATE Info SET Naam= '$Naam' , Email= '$Email' , Pand= '$Pand' , Huisnummer= '$Huisnummer' , Deel= '$Deel' WHERE id=$id")){
    echo("Error description:". mysqli_error($db));
    }
    header('location: overzichtlocatie.php');
}
// retrieve records
$results = mysqli_query($db, "SELECT *  FROM Info");
?>

What I have tried:

I have tried the code like this:

<pre>
<?php
// connect to database
$db = mysqli_connect("xxx","xxx","xxx","xxx");
//update records
if (isset($_POST['aanpassen'])) {
    $Naam = mysqli_real_escape_string($_POST["Naam"]);
    $Email = mysqli_real_escape_string($_POST["Email"]);
    $Pand = mysqli_real_escape_string($_POST["Pand"]);
    $Huisnummer = mysqli_real_escape_string($_POST["Huisnummer"]);
    $Deel = mysqli_real_escape_string($_POST["Deel"]);
    $id = mysqli_real_escape_string($_POST["id"]);

    if (!mysqli_query($db,"UPDATE Info SET Naam= '$Naam' , Email= '$Email' , Pand= '$Pand' , Huisnummer= '$Huisnummer' , Deel= '$Deel' WHERE id=$id")){
    echo("Error description:". mysqli_error($db));
    }
    header('location: overzichtlocatie.php');
}
// retrieve records

?>

Posted 17-Mar-20 7:19am

533578

Updated 17-Mar-20 10:45am

Add a Solution

Comments

Richard Deeming 17-Mar-20 13:34pm

Firstly, the "escape string" functions aren't really an adequate defence against SQL Injection. You should be using prepared statements with parameters instead.

PHP: SQL Injection - Manual[^]

533578 17-Mar-20 13:56pm

I have tried multiple things, and it gives me this error still.. Even with prepared statements.

phil.o 17-Mar-20 14:13pm

Please use the green "Improve question" widget and qualify it with the prepared-statement version.

Richard Deeming 17-Mar-20 13:35pm

Secondly, you're storing passwords in plain text. Don't do that.
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

PHP even has built-in functions to help you do the right thing:
PHP: password_hash[^]
PHP: password_verify[^]

533578 17-Mar-20 13:45pm

It is only made for myself and my teacher. Because it is for a school project ;)

phil.o 17-Mar-20 13:48pm

This does not prevent you from doing it right :)

533578 17-Mar-20 13:54pm

True, but I am just getting started with php so I need to learn a lot of things. And school gave me this order, and no one can help me because all the schools are closed. Now I need to ask people on sites like this. And I am 2 days stuck with this error :(

ZurdoDev 17-Mar-20 15:05pm

You likely have a ' in one of your fields causing you sql statement to break. I don't do php and mysql but w3schools has a good article on how to do it.

https://www.w3schools.com/php/php_mysql_prepared_statements.asp

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2020-03-17T10:46:00

Quote:
I need help with SQL syntax error.

1 of your problems is that you can't know what is the query.
I would start by replace

PHP

if (!mysqli_query($db,"UPDATE Info SET Naam= '$Naam' , Email= '$Email' , Pand= '$Pand' , Huisnummer= '$Huisnummer' , Deel= '$Deel' WHERE id=$id")){

by something like

PHP

$Query= "UPDATE Info SET Naam= '$Naam' , Email= '$Email' , Pand= '$Pand' , Huisnummer= '$Huisnummer' , Deel= '$Deel' WHERE id=$id";
if (!mysqli_query($db,$Query)){

This little change allow you to print $Query or inspect it with debugger. This way, you can know what was your real query.
-----
Your code do not behave the way you expect, or you don't understand why !

There is an almost universal solution: Run your code on debugger step by step, inspect variables.
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't know what your code is supposed to do, it don't find bugs, it just help you to by showing you what is going on. When the code don't do what is expected, you are close to a bug.
To see what your code is doing: Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.

The downside of this solution:
Debugger - Wikipedia, the free encyclopedia[^]

Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
Basic Debugging with Visual Studio 2010 - YouTube[^]

phpdbg | php debugger[^]
Debugging techniques for PHP programmers[^]

The debugger is here to only show you what your code is doing and your task is to compare with what it should do.