Why this mysql insert statement doesn't work?

Question

I am trying to insert an image into a longblob type field named 'img':

    include('config.php');

    $imgData =  mysqli_real_escape_string($conn, file_get_contents($_FILES['im_age']  ['tmp_name']));

    $qry="  insert into tblproducts(
    vendorid, prefix, overallsize, handlematerial, img, code, itemcost, shippingcost,
    profitpercentage, discountpercentage, caption, availability, quantity
    ) 
    values(
    $_POST[vendor_id],
    $_POST[pre_fix],
    $_POST[overall_size],
    $_POST[handle_material],
    $imgData,
    $_POST[co_de],
    $_POST[item_cost],
    $_POST[shipping_cost],
    $_POST[profit_percentage],
    $_POST[discount_percentage],
    $_POST[cap_tion],
    $_POST[avail_ability],
    $_POST[quan_tity]
    )
";

   $result = mysqli_query($conn, $qry) or die("Could not execute query!");

EDIT: I get the following error: 'Could not execute query'

How do I make the insertion of the image happen? Thanks in advance!

Technically it's not error - it's your message. The error message would contain at least description of what has happened. — zerkms, Jul 08 '14 at 03:50
You're right ... the query does not insert the record though — user3762808, Jul 08 '14 at 03:52
If you want your way of code, try to put single quotes in your VALUES() — Wesley Brian Lachenal, Jul 08 '14 at 03:53
I'm not sure what you're trying to do. you are treating binary image data as if it were a string and trying to insert it into a field in the DB (note that escaping the data may make it unusable). Besides the problem of SQL injection (what happens if someone POST's code that will break your query, and do other malicious things instead?), there's also the issue with the binary data, which you are just inserting directly into the query and could cause it to break. Also, you should probably upload the image separate and store the location of the image in the DB instead. — serakfalcon, Jul 08 '14 at 03:53

score 2 · Accepted Answer · answered Jul 08 '14 at 03:51

Your POST values aren't being interpolated properly. You shouldn't be using interpolation or concatenation to build your queries. Here's how to write a prepared statement to prevent against SQL injections:

$qry = "INSERT INTO tblproducts (
    vendorid, prefix, overallsize, handlematerial, img, code, itemcost, shippingcost,
    profitpercentage, discountpercentage, caption, availability, quantity
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

$stmt = mysqli_prepare($conn, $qry);

mysqli_stmt_bind_param($stmt, 'sssssssssssss', 
    $_POST['vendor_id'],
    $_POST['pre_fix'],
    $_POST['overall_size'],
    $_POST['handle_material'],
    $imgData,
    $_POST['co_de'],
    $_POST['item_cost'],
    $_POST['shipping_cost'],
    $_POST['profit_percentage'],
    $_POST['discount_percentage'],
    $_POST['cap_tion'],
    $_POST['avail_ability'],
    $_POST['quan_tity']
);

if (!mysqli_stmt_execute($stmt)) {
    echo 'Query failed.';
}

Of course there's more advanced techniques of error handling and using object-oriented things, but this should cover the very basics to get the query working.

You may have noticed the question mark parameters in the query. MySQLi does not support named parameters. If you would prefer using named parameters in your query like :vendor_id, try using PDO.

score 2 · Answer 2 · edited May 23 '17 at 10:32

Regarding your query: none of the post data would be parsed into the query because you are just writing it. You need to either concatenate the values in or wrap the variables in {$var}. Also the key in $_POST should be wrapped in single quotes. Ex: $_POST['vendor_id'];

Use mysqli_prepare() and mysqli_bind_param() to make a more functional and more secure query.

Regarding images in MySQL, you can but it's not recommended. Check out this post for more info about it.

score 1 · Answer 3 · answered Jul 08 '14 at 03:51

Its because you are munging the image datadirectly into the query text which is probably causing a bunch of issues.

Use mysqli_prepare and mysqli_stmt_bind_param instead. As Dai hinted at, this is much safer.

Why this mysql insert statement doesn't work?

3 Answers3