PHP, making MySQL statements and the use of quotes

Question

After setting up a mysqli object in php, i want to make to insert some POST variables into a table, but I have a question about how the quotes will work out:

$sql = "INSERT INTO whatever (a, b, c)
 VALUES ('$_POST[a]','$_POST[b]','$_POST[c]')";

I'm aware, however, that most times I've used global variables like POST or GET, there are quotes around the variable name -- my question is do I have to so in the sql statement above? Should I then escape single or double quotes around those variable names? Not sure if quotes are even necessary...

Answer 1

You would do it like this:

VALUES ('{$_POST['a']}','{$_POST['b']}','{$_POST['c']}')";

You need the quotes around the variables if they are strings, it doesnt break if you quote an integer though so you can use quotes all the time if you like.

Make sure to escape/clean the global variables, don't trust user data remeber :) PDO comes with the cleaning function prepare for instance.

Answer 2

Since you are using MySQLi already, why not use a prepared statement?

if ($stmt = $mysqli->prepare('INSERT INTO whatever (a,b,c) VALUES (?,?,?)') {
   $stmt->bind_param('sss', $_POST['a'], $_POST['b'], $_POST['c']);
   ....

This will take care of the quotes for you automatically and securely (against SQL injection).

See http://www.php.net/manual/en/mysqli-stmt.bind-param.php for example usage.

Answer 3

if you are putting values into a mysql database then you need to put them in "s or 's (unless they are numbers).

Post variables work like arrays and as such you have to call them like this: $_POST['a'] $_POST['b'] etc. So therefore putting them into a mysql query (even though the query is enclosed in double quotes) won't work.

You will have to do this:

$sql = "INSERT INTO whatever (a, b, c) VALUES ('".$_POST['a']."','".$_POST['b']."','".$_POST['c']."')";

Or if you want to embed the variables you could do this:

for($_POST as $key => $val){
    $$key = $val;
}

and then do this:

$sql = "INSERT INTO whatever (a, b, c) VALUES ('$a','$b','$c')";

Answer 4

You need to use mysqli_real_escape_string() to sanitize the input first.

Suggestion:

$a = mysqli_real_escape_string($_POST["a"]);
$b = mysqli_real_escape_string($_POST["b"]);
$c = mysqli_real_escape_string($_POST["c"]);

$sql = "INSERT INTO whatever (a, b, c) VALUES ('$a','$b','$c')";

Alternatively (better) use a wrapper that supports prepared statements.

Edit: ah, mysqli does support prepared statemtns. See Kenny's answer.

Answer 5

Quotes primarily means to group something, either string or something else. For example you are inserting Hello I am someone to the database then your query would be like this INSERT INTO tables VAlueS (Hello I am someone, other data, again another data) this will not clarify your parameters.

Escaping the quotes are good option, but only if you are using the same quotes inside a quote for example

$sql = "INSERT INTO whatever (a, b, c)
 VALUES ("$_POST[a]","$_POST[b]","$_POST[c]")";

In this case, it will render error, so you have to escape your quotes.