Inserting Items into a MySQL Database from Form

Question

Through two pages on a website that I don't plan to ever leave my home computer, I want to use a form to input items into a MySQL database also hosted on my computer. I've done this before with something nearly identical, but for some reason this one isn't working. I'm not worried about the security of this or anything like that as it won't be leaving my own computer, I just want it to actually work.

Form:

<form action='addclothes.php' method='post'><table style="font-family:verdana;font-size:14px;color:#004766;"><tr><td>
Type of clothing:</td><td><select name="type">
<option value="0">---</option>
<option value="dresses">Dress</option>
<option value="tops">Top</option>
<option value="bottoms">Bottom</option>
<option value="shoes">Shoes</option>
<option value="accessories">Accessory</option></select></td></tr>
<tr><td>Name:</td><td><input type="text" name="name"></td></tr>
<tr><td>Path to full image:</td><td><input type="text" name="largeimagepath"></td></tr>
<tr><td>Path to thumbnail:</td><td><input type="text" name="smallimagepath"></td></tr>
<tr><td colspan="2"><center><input type="submit" value="Submit" name="submit"></center></td></tr>
</table></form>

That sends over to addclothes.php, which looks like this, encased in html to keep the same layout:

<?php

$name = $_POST['name'];
$table = $_POST['type'];
$largepath = $_POST['largeimagepath'];
$thumbpath = $_POST['smallimagepath'];

    $db = mysql_connect("localhost", "root", "******") or die(mysql_error());
    mysql_select_db("Default") or die(mysql_error());

    $query = "INSERT INTO clothes."{$table}" (name, imagepath, thumbimagepath)
 VALUES("{$name}", "{$largepath}", "{$thumbpath}")";
    mysql_query($query) or die(mysql_error()); ?>

<p>Item Added!</p>

It comes to the next page and just says "Item added" no matter what. If I try to echo the query just after I create the variable that doesn't show up either.

You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Mar 21 '13 at 14:00
You have syntax errors in your string concatenation that I would expect to make PHP throw a parse error. (This doesn't actually matter in the grand scheme of things since, as per my previous comment, you should eliminate string concatenation from your query building.) — Quentin, Mar 21 '13 at 14:01

jeroen · Answer 1 · 2013-03-21T14:09:13.617

This is wrong:

$query = "INSERT INTO clothes."{$table}" (name, imagepath, thumbimagepath)
            VALUES("{$name}", "{$largepath}", "{$thumbpath}")";

You would need to use single quotes inside the query to avoid breaking it (you don't quote the table name; if it can be a reserved word in mysql you use backticks for that):

$query = "INSERT INTO clothes.`{$table}` (name, imagepath, thumbimagepath)
            VALUES('{$name}', '{$largepath}', '{$thumbpath}')";

Also note that security / sql injection is not just to protect your from people with bad intentions; if you don't prepare your data properly to be used in an sql query, even valid data - entered by you yourself - can break the query / application if the name for example contains a ' character (O'Neill for example...).

So safety is always important and that is why you should switch to PDO (or mysqli) and prepared statements. Apart from that the mysql_* functions are deprecated.

One last comment: Should you open your site to the outside world, no preparing or escaping will secure the table name in your query; you would need to check against a list of allowed table names to avoid sql injection.

score 0 · Answer 2 · answered Mar 21 '13 at 15:42

<?php
    $name = $_POST['name'];
    $table = $_POST['type'];
    $largepath = $_POST['largeimagepath'];
    $smallpath = $_POST['smallimagepath'];

    $name = htmlentities($name);
    $table = htmlentities($table);
    $largepath = htmlentities($largepath);
    $smallpath = htmlentities($smallpath);

    $connection = new PDO('mysql:host=localhost;dbname=Default','root','*****');
    $query = $connection->prepare('INSERT INTO :table (name,imagepath,thumbimagepath) VALUES (:name,:image,:thumb)';

    $query->bindParam(':table', $table);
    $query->bindParam(':name', $name);
    $query->bindParam(':image',$largepath);
    $query->bindParam(':thumb',$smallpath);
    $query->execute();

    if($query->rowCount()) {
        echo "Inserted correctly";
    } else {
        echo "Failure inserting";
    }
?>

And as others have said, you really should not be allowing someone to input the table name through a form.

Inserting Items into a MySQL Database from Form

2 Answers2