Inserting Data from dropdown into database with PHP

Question

First I needed a dropdown list that I could update easily so I created a database called manufacturers where I list manufacturers to be selected in a form.

I finally accomplished this with this code:

<?php
 // Connect to the test datbase on localhost
 // That's where we created the countries table above
 mysql_connect('localhost','##user##','##pass##');  mysql_select_db('wordpress');

 // Query the countries table and load all of the records
 // into an array.
 $sql = 'select * FROM manufacturers';
 $res = mysql_query($sql) or die(mysql_error());
 while ($rec = mysql_fetch_assoc($res))
 $manufacturers[] = $rec;
 ?>
<form action="select.php" method="post">
<?php
 echo '<select name="dropdown">';
 foreach ($manufacturers as $c)
{
  if ($c['id'] == $_GET['id'])
   echo "<option value=\"{$c['meta_id']}\" selected=\"selected\">{$c['meta_value']}              </option>\n";
 else
  echo "<option value=\"{$c['meta_id']}\">{$c['meta_value']}</option>\n";
 }
echo '</select>';
?>
 <input type="submit" value="Submit" name="submit"/>
 </form>

This worked out great I now have a dropdown list that is populated from my database manufacturers.

Now I need to send this to an existing database call post_meta so that from there I can display the users selection permanently.

I have tried a couple of different options but I am trying to use the following code to send this to my post_meta database.

<?php
$con = mysql_connect("localhost","##user##","##pass##");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("wordpress", $con);

$sql="INSERT INTO wp_postmeta (meta_id, post_id, meta_key, meta_value)
VALUES
('$_POST['meta_id']}','$_POST[post_id]','$_POST[meta_key]','$_POST[meta_value]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
?>

This actually inserts into the database but doesn't record any values.

Please help me figure out what I'm doing wrong.

You are seriously wide open to SQL injection attacks. You should really consider learning prepared queries with PDO. Also, what's up with that extra bracket after `$_POST['meta_id']`? Have you tried `print_r($_POST)` to make sure the post data is getting there? — Brad, Nov 03 '11 at 19:24

Johan · Accepted Answer · 2011-11-03T20:05:57.943

1

The proper way to do this is to A: escape all those $_POST superglobals.
and B. Write a query as shown below.

Here's the tabledef for wp_postmeta:
http://codex.wordpress.org/Database_Description#Table:_wp_postmeta

Because meta_id is an auto_increment primary key, you do not provide it, MySQL does.

//$meta_id = mysql_real_escape_string($_POST['meta_id']);  <<-- not needed.
$post_id = mysql_real_escape_string($_POST['post_id']);
$meta_key = mysql_real_escape_string($_POST['meta_key']);
$meta_value = mysql_real_escape_string($_POST['meta_value']);
$sql=" INSERT INTO wp_postmeta
       (post_id, meta_key, meta_value)
       VALUES
       ('$post_id','$meta_key','$meta_value') ";  //<<-- don't forget the quotes!
if ($result = mysql_query($sql)) {
  //You can get the new meta_id using:
   $new_meta_id = mysql_insert_id($result);
} else { 
   die ("could not insert ".mysql_error());
}

edited Nov 03 '11 at 20:05

answered Nov 03 '11 at 19:55

Johan

74,508
24
191
319

Thanks for your help Johan but I am still faced with the same problem.I get this errorNotice: Undefined index: post_id in C:\xampp\htdocs\wordpress\select.php on line 11 Notice: Undefined index: meta_key in C:\xampp\htdocs\wordpress\select.php on line 12 Notice: Undefined index: meta_value in C:\xampp\htdocs\wordpress\select.php on line 13 and still no data is found. if I echo the results it show that the data is being passed. – Jakeray Nov 03 '11 at 20:15
$_post['x'] refers to the fields of your form make a form using those fields. I suggest asking a new question how to get values in $_post vars. – Johan Nov 04 '11 at 05:14

score 0 · Answer 2 · answered Nov 03 '11 at 19:34

0

Do none of your values show up? It looks like you're missing quotes around your key values. For example, shouldn't it be :

$_POST['post_id']

To do a sanity check, just echo your $_POST variables as opposed to doing the insert right away. This will help you figure out if you've got some syntax wrong. Also I'd read Brad's comment and keep it in mind for the future.

answered Nov 03 '11 at 19:34

Jeffery Smith

209
2
5

No the values do not show up. I get this error for all four of my values: Notice: Undefined index: meta_id – Jakeray Nov 03 '11 at 19:42
-1 For SQL-injectable answer. It's bad enough the questions are littered with them. It's really not acceptable in answers. See: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain – Johan Nov 03 '11 at 19:42
@Johan Brad had already pointed it out which is why I referenced his comment in my answer. I didn't realize that wasn't enough, my apologies. – Jeffery Smith Nov 03 '11 at 19:50

score -1 · Answer 3 · answered Nov 03 '11 at 19:40

-1

Try this query:

$sql="
INSERT INTO wp_postmeta
(meta_id, post_id, meta_key, meta_value)
VALUES
(
    '{$_POST['meta_id']}',
    '{$_POST['post_id']}',
    '{$_POST['meta_key']}',
    '{$_POST['meta_value']}'
)
";

And, as people say in comments, this code is very vulnerable, please consider to find better option to pass variables into query.

answered Nov 03 '11 at 19:40

Vitaly Dyatlov

1,872
14
24

-1 How hard can it be to put `mysql_real_escape_string()` in the answer and just use safe code. `Show, don't tell`. – Johan Nov 03 '11 at 19:43
Doesn't sql injections only work when users are allowed input data from a text field? I am only allowing users to select one option form the dropdown list. – Jakeray Nov 03 '11 at 19:59
I've use mysql_real_escape_string() in lots of code, never seen or heard about the issue you're describing, unless you are also using `magic_quotes` another big security fail. And if you're big on PDO, that's cool 'cause PDO rules, but use it in the code sample. Oh and you don't insert an explicit value for `meta_id` it's an auto_increment. – Johan Nov 03 '11 at 20:01
@Jakeray, All superglobals `$_....` should be escaped. Always. There are many tools to manipulate $_POST, $_GET and alike vars, in ways you cannot easily oversee. – Johan Nov 03 '11 at 20:02

Inserting Data from dropdown into database with PHP

3 Answers3

Linked