Insert row to MySQL Table using PHP Form

Question

I would like a user to be able to insert a "bid" into a MySQL table using a php form - this is only for demo, not live purpose. I get the following error message,

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''90','2011-07-13'' at line 3 (Line 3 refers to my tag?) I figure it doesnt like the form inputs just being "text" type, but no idea how to fix it - all advice very welcome, this is my form & php code below;

<form action="insert.php" method="post">
<div><label for="commodity">Commodity</label><input type="text" name="commodity"/></div>
<div><label for="region">Region</label><input type="text" name="region"/></div>
<div><label for="member">Member</label><input type="text" name="member" /></div>
<div><label for="size">Size</label><input type="int" name="size" /></div>
<div><label for="price">Post Bid</label><input type="decimal" name="price" /></div>
<div><label for="posted">Date Posted</label><input type="text" name="posted"/></div>
<P><label for="submit">Submit Bid</label><input type="submit" /></P>
</form>

& php

<?php
$con = mysql_connect("localhost","","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("palegall_newTrader", $con);
$sql="INSERT INTO `buy` (commodity, region, member, size, price, posted)
VALUES
('$_POST[commodity]','$_POST[region]','$_POST[member]','$_POST[size]','$_POST[price]','$_POST[posted]'";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
mysql_close($con)
?>

Many thanks in advance, scotia

Just using post variables into a query is dangerous (what if they include the ' character and change the code thats executed).. Consider using [parameterized queries](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php) — Bob Vale, Jul 21 '11 at 14:17
ending round bracket misplaced ...$sql="INSERT INTO `buy` (commodity, region, member, size, price, posted) VALUES ('$_POST[commodity]','$_POST[region]','$_POST[member]','$_POST[size]','$_POST[price]','$_POST[posted]')"; — Adam MacDonald, Jul 21 '11 at 14:17
Thank you Bob & Adam - In new to this (obviously) & dont understand about sanatising & injection as yet (but peeked at link supplied - thank you) & for the corrections, much appreciated. — scotia, Jul 21 '11 at 14:26

score 1 · Answer 1 · answered Jul 21 '11 at 14:15

You're vulnerable to SQL injection, and your POST probably contains a ', which is causing the syntax error. Try the following:

$commodity = mysql_real_escape_string($_POST['commodity']);
$region = mysql_real_escape_string($_POST['region']);
etc...

$sql = "INSERT INTO ... VALUES ('$commodity', '$region', etc...)";

the escape function will ensure that any SQL metacharacters in the data are escaped, so they can't "break" your query. Never EVER directly insert user-provided data into an SQL query, even if it's a simple script that only you will ever use. Get into the habit of escaping everything (or better yet, using PDO prepared statements), because at some point, you'll get burned if you don't.

Thank you Marc - Im not really sure where to place the $commodity etc, Im sorry - ? — scotia, Jul 21 '11 at 14:28

score 1 · Answer 2 · answered Jul 21 '11 at 14:16

Your closing parenthesis need to go after the last value to be inserted, now it's after the 4th element. Put it at the and of the statement.

$sql="INSERT INTO `buy` (commodity, region, member, size, price, posted)
VALUES
('$_POST[commodity]','$_POST[region]','$_POST[member]','$_POST[size]','$_POST[price]','$_POST[posted]')"

Also, follow @Marc's advice and sanatize your input.

score 1 · Answer 3 · answered Jul 21 '11 at 14:17

1

Shouldn't it be

$sql="INSERT INTO `buy` (commodity, region, member, size, price, posted) VALUES ('$_POST[commodity]','$_POST[region]','$_POST[member]','$_POST[size]','$_POST[price]','$_POST[posted]')";

answered Jul 21 '11 at 14:17

Rahul

76,197
13
71
125

score 1 · Answer 4 · answered Jul 21 '11 at 14:20

1

There is a misplaced parenthesis after $_POST['size'] that should be after $_POST[posted]

The SQL should look like this:

$sql="INSERT INTO `buy` (commodity, region, member, size, price, posted)
VALUES
('$_POST[commodity]','$_POST[region]','$_POST[member]','$_POST[size]','$_POST[price]','$_POST[posted]')";

answered Jul 21 '11 at 14:20

Martin Taleski

6,033
10
40
78

Many thanks - this does remove the error message & echo's the record was added. – scotia Jul 21 '11 at 14:29

Insert row to MySQL Table using PHP Form

4 Answers4