MySQL error because of syntax in Custom PHP code

Question

I am trying to enter user's data into a database. I think the commas in the address are causing the error.

<?php 
 $full_name = $_POST["fullname"]; 
 $email = $_POST["email"]; 
 $password = $_POST["password"]; 
 $full_address = $_POST["address"]; 
 $city = $_POST["city"]; 
 $age = $_POST["age"]; 
 $contact_number = $_POST["number"]; 
 $gender = $_POST["gender"]; 
 $education = $_POST["education"]; 

?>

<?php
$servername = "hidden";
$username = "hidden";
$password = "hidden";
$dbname = "hidden";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO users (full_name, email, password,full_address,city,age,contact_number,gender,education)
VALUES ($full_name, $email, $password,$full_address,$city,$age,$contact_number,$gender,$education)";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

+1 PDO is the way to go. Using combination of `sprintf()` and `addslashes()` is a poor-man's SQL injection attack protection which is better than nothing. — jpaljasma, Oct 22 '15 at 18:06
possible duplicate of http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks and http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Funk Forty Niner, Oct 22 '15 at 18:07

score 4 · Accepted Answer · edited Oct 28 '15 at 18:23

4

As others have noted, your code is vulnerable to SQL injections. You should consider using parameterized queries:

$sql = "INSERT INTO users (full_name, email, password, full_address, city, age, contact_number, gender, education)
        VALUES (?,?,?,?,?,?,?,?,?)";

$stmt = mysqli_prepare($conn, $sql);

// Bind parameters
$stmt->bind_param("s", $full_name);
$stmt->bind_param("s", $email);
$stmt->bind_param("s", $password);
$stmt->bind_param("s", $full_address);
$stmt->bind_param("s", $city);
$stmt->bind_param("s", $age);
$stmt->bind_param("s", $contact_number);
$stmt->bind_param("s", $gender);
$stmt->bind_param("s", $education);

if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

For more information refer to the PHP manual on MySQLi prepared statements.

edited Oct 28 '15 at 18:23

answered Oct 22 '15 at 18:05

rink.attendant.6

44,500
61
101
156

Remove the parameter from `execute()`. I have fixed it in my code example. – rink.attendant.6 Oct 22 '15 at 18:57
Do I have to keep the `INSERT` query as it is (with the ? in them) or I have to replace them with my variables? – Oct 22 '15 at 18:59
Yes you keep the insert query as-is with `?` placeholders. Your variables are bound using [`bind_param`](https://php.net/manual/en/mysqli-stmt.bind-param.php). – rink.attendant.6 Oct 22 '15 at 19:01
I have a question. Why didn't you use `mysqli_prepare()`? – Oct 28 '15 at 18:28

score 2 · Answer 2 · edited Oct 22 '15 at 18:13

2

You need to quote string in your SQL statement;

$sql = "INSERT INTO users (full_name, email, password,full_address,city,age,contact_number,gender,education)
VALUES ('$full_name', '$email', '$password','$full_address','$city',$age,'$contact_number','$gender','$education')";

Notice the single quotes around all the variables that contain strings. I might be a bit off because I don't know the values or table structure. But the just quote all values that are going in to a Date or Text field.

To avoid additional problems and security risks you should be using mysqli_real_escape_string (at a minimum).

In all your assignment statements wrap the values in mysqli_real_escape_string

$full_name = mysqli_real_escape_string($conn, $_POST["fullname"]); 
$email = mysqli_real_escape_string($conn, $_POST["email"]);
...

Note this requires setting up your DB connection before the variable assignments, so you'll have to reorganize your code a bit.

rink.attendant.6's answer is the proper way to adapt your code.

edited Oct 22 '15 at 18:13

Funk Forty Niner

74,450
15
68
141

answered Oct 22 '15 at 18:01

Dan

10,614
5
24
35

OP is using mysqli, and should be using bound parameters instead; just putting quotes around the values will fail if there are apostrophes in the data, and leaves the code wide open to SQL injection – andrewsi Oct 22 '15 at 18:02
[`mysqli_real_escape_string($_POST["fullname"])`](http://stackoverflow.com/revisions/33287612/2) that requires a db connection as the first parameter http://php.net/manual/en/mysqli.real-escape-string.php – Funk Forty Niner Oct 22 '15 at 18:08
I personally wouldn't use `mysqli_real_escape_string` since it, as mentioned above, requires active database connection and **requires network round-trip** which will bite you back when mysql is not localhost but other server in different availability zone. – jpaljasma Oct 22 '15 at 21:32

MySQL error because of syntax in Custom PHP code

2 Answers2