Inserting $variable or $_POST value into mysql table

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

You can insert $_POST directly in a query, but **[you shouldn't](http://en.wikipedia.org/wiki/SQL_injection)** — Damien Pirsy, Mar 08 '12 at 13:00
The solution you are looking for is just a **basic syntax.** Learning the syntax of the language you are using is essential. — Your Common Sense, Mar 08 '12 at 13:29

Quentin · Accepted Answer · 2012-03-08T13:33:04.403

7

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.

edited Mar 08 '12 at 13:33

answered Mar 08 '12 at 12:58

Quentin

914,110
126
1,211
1,335

I understand that there are security risks, but i'm doing this on my local web server in my LAN only to understand how it works, it is not being used anywhere where security is an issue. Thank you for the good answer. – Sergei Mar 08 '12 at 13:15
A typo in the input data could cause problems with the data, so trusting all the users is not a sensible idea. Doing things The Right Way is not arduous and you should make it a habit to follow best practises. – Quentin Mar 08 '12 at 13:18
2

@Sergei this is just wrong answer. There is no security issue but only formatting issue. Strings in the SQL query **should be quoted and escaped**, or you will have a malformed query. This is a **syntax** rule and you have to follow it **always**. – Your Common Sense Mar 08 '12 at 13:25

chilly · Answer 2 · 2012-03-08T13:09:41.140

5

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.

edited Mar 08 '12 at 13:09

answered Mar 08 '12 at 13:01

chilly

304
2
13

The code here has [a security vulnerability](http://bobby-tables.com/). Make sure you pay attention to the last paragraph of the answer. – Quentin Mar 08 '12 at 13:02

score 1 · Answer 3 · answered Mar 08 '12 at 12:59

1

Seems like you're not escaping and quoting your arguments to mysql properly.

To insert variables in MySQL you need to escape them at least: $var = mysql_real_escape_string($_POST['variable']) and then ".. VALUES ('".$var."')"

You should also probably consider using libraries for connecting to MySQL like DOCTRINE: http://www.doctrine-project.org/ that handles this for you.

answered Mar 08 '12 at 12:59

Marius Grigaitis

2,520
3
23
30

PDO (or mysqli) would be enough, a full-fledged ORM like doctrine could be too much for simple tasks – Damien Pirsy Mar 08 '12 at 13:02

score 0 · Answer 4 · edited May 30 '17 at 09:16

0

Use this solution, its 100% works

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

when you use {}, you dont need write value in ' '

edited May 30 '17 at 09:16

Robert

5,278
43
65
115

answered May 30 '17 at 08:55

Андрей Павлов

17
1

score -1 · Answer 5 · edited Feb 22 '18 at 11:22

-1

$var1=$_POST['variable_name1'];
$var2=$_POST['variable_name2'];
$q="INSERT INTO `users` (`column 1`, `column2`) VALUES ($var1, $var2)";

$result=mysql_query($q);

edited Feb 22 '18 at 11:22

proofzy

627
1
12
23

answered Apr 24 '15 at 04:52

Ami Padsala

19
3

Thomas · Answer 6 · 2012-03-08T13:05:37.403

-1

Your variables are not recognized as variables. They are a part of your string.

Try:

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

Same for your second problem.

edited Mar 08 '12 at 13:05

answered Mar 08 '12 at 12:58

Thomas

98
6

That won't fix the problem either because it still isn't inserting quotes into the final SQL. – Quentin Mar 08 '12 at 13:03
Edits mean that the code now works, but it still has the SQL injection problem. – Quentin Mar 08 '12 at 13:13

score -1 · Answer 7 · edited Mar 08 '12 at 13:09

-1

Because the POST variables have ' in them, you have to concatenate instead.

I.E.

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

Or

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

It's also a good idea to put quotes around the variables, in case its empty (or a string rather than an integer)

edited Mar 08 '12 at 13:09

Damien Pirsy

25,319
8
70
77

answered Mar 08 '12 at 12:58

Benno

3,008
3
26
41

**Danger**: This has [a security vulnerability](http://bobby-tables.com/). It won't work anyway, the final SQL needs quotes around the values. – Quentin Mar 08 '12 at 13:03

Riz · Answer 8 · 2012-03-08T13:04:40.260

-1

mysql_select_db("webuser1", $con);

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

mysql_close($con);

When not using Apostrophes around values, it is supposed to be non string value.

edited Mar 08 '12 at 13:04

answered Mar 08 '12 at 12:59

Riz

9,703
8
38
54

Inserting $variable or $_POST value into mysql table

8 Answers8

Linked