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 — Bob Vale
– Bob Vale, Commented Jul 21, 2011 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
– Adam MacDonald, Commented Jul 21, 2011 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
– scotia, Commented Jul 21, 2011 at 14:26

Marc B · Accepted Answer · 2011-07-21 14:15:25Z

1

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.

answered Jul 21, 2011 at 14:15

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

scotia Over a year ago

Thank you Marc - Im not really sure where to place the $commodity etc, Im sorry - ?

Jacob · Accepted Answer · 2011-07-21 14:16:59Z

1

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.

answered Jul 21, 2011 at 14:16

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Comments

Rahul · Accepted Answer · 2011-07-21 14:17:05Z

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, 2011 at 14:17

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Martin Taleski · Accepted Answer · 2011-07-21 14:20:22Z

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, 2011 at 14:20

Martin Taleski

6,47811 gold badges43 silver badges87 bronze badges

1 Comment

scotia Over a year ago

Many thanks - this does remove the error message & echo's the record was added.

Collectives™ on Stack Overflow

Insert row to MySQL Table using PHP Form

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related