Inserting $variable or $_POST value into mysql table [duplicate]

Question

My question concerns why one piece of code works and two that does not, and how i can get the code that does not work to work.

The code that works:

mysql_select_db("webuser1", $con);

mysql_query("INSERT INTO users (column 1, column2) VALUES ('value1', 'value2')");

mysql_close($con);

Code no1 that does not ($var1 contains 'value1' etc.):

mysql_select_db("webuser1", $con);

mysql_query("INSERT INTO users (column 1, column2) VALUES ($var1, $var2)");

mysql_close($con);

And code no2 that does not work ($_POST['value1'] contains 'value1' etc.):

mysql_select_db("webuser1", $con);

mysql_query("INSERT INTO users (column 1, column2) VALUES ($_POST['value1'], $_POST['value2'])");

mysql_close($con);

Am i not supposed to be able to insert $var or $_POST in mysql? I hope you do not find this Q stupid but i have been looking around for solutions but i have not understood them. Thank you

Answer 1

In SQL, string values need to be quoted:

VALUES ('value1', 'value2')"

When you use variables:

VALUES ($var1, $var2)");

They are not quoted … unless the quotes are in the values themselves.

So if $var1 = 'value1'; $var2 = 'value2' then (after the variables are interpolated in your string) your SQL looks like this:

VALUES (value1, value2)"

You could resolve your immediate problem by adding quotes:

VALUES ('$var1', '$var2')");

but this doesn't fix your major security vulnerability and lets your data break the query in different ways.

You should avoid creating SQL statements by assembling strings from variables. This way leads to SQL Injection security holes. Use an interface that supports bound arguments. They will handle quoting and escaping for you.

Answer 2

mysql needs single quotes to enclose a string... so you would need something like this:

mysql_query("INSERT INTO users (column 1, column2) VALUES ('".$_POST['value1']."', '".$_POST['value2']."')");

for everything that is not a string you won't need the single quotes (')

as mentioned before you should not forget to escape strings that you want to put into the database. for example use prepared statements. by binding the parameters it is ensured that your passed value is of the type you specified within the prepared statement.