Learning mysqli but have a few questions

I am trying to convert my site from mysql to mysqli but I am a little confused on a few things.

First of is for example the update database string. I know it should be like this (pulled from a tutorial site)

$stmt = $mysqli->prepare("UPDATE movies SET filmName = ?, 
   filmDescription = ?, 
   filmImage = ?,  
   filmPrice = ?,  
   filmReview = ?  
   WHERE filmID = ?");
$stmt->bind_param('sssdii',
   $_POST['filmName'],
   $_POST['filmDescription'],
   $_POST['filmImage'],
   $_POST['filmPrice'], 
   $_POST['filmReview'],
   $_POST['filmID']);
$stmt->execute(); 
$stmt->close();

Ok, I know that the ssddii stand for String, Decimal, Interger

My question is, I have images being stored in my database and they type “longbow”. How would those be handled in the update query and is the VarChar the “string”?

For example if I was to use this for my site, the update query would look something like

$stmt = $mysqli->prepare("UPDATE new-equip SET itemName = ?, 
   model = ?, 
   serial = ?,  
   desc = ?,    
   WHERE itemID = ?");
$stmt->bind_param('ssssi',
   $_POST['itemName'];
   $_POST['model'],
   $_POST['serial'],
   $_POST['desc'],
   $_POST['itemID']);
$stmt->execute(); 
$stmt->close();

Am I close?

Basically everything that would normally be enclosed in quotes if it were in the query itself is a “string” as far as the ‘s’ is concerned.

Where ever you got the example code using $_POST in the database call is providing bad examples. You should always validate the $_POST variables and store the validated values into new variables long before they get anywhere near a database call - that is unless you want your database to end up filled with meaningless junk.

1 Like

Very much appreciated. I got the example from the site below.

http://www.mustbebuilt.co.uk/php/insert-update-and-delete-with-mysqli/

I guess I will keep looking around until I find a recent thread / tutorial that explains it better.

I would validate the form before I do anything with the database as felgall has said. Also, I’m not sure if it matters, but you should stuff the $_POST values into the query like that.

Something like this is acceptable.

$stmt = $mysqli->prepare("UPDATE new-equip SET itemName = ?, model = ?, serial = ?, desc = ?, WHERE itemID = ?");
$stmt->bind_param('ssssi', $itemName, $model, $serial, $desc, $itemID);
$itemName = $_POST['itemName'];
$model = $_POST['model'],
$serial = $_POST['serial'],
$desc = $_POST['desc'],
$itemID = $_POST['itemID'];
$stmt->execute();
$stmt->close();

Note: You don’t have to escape any of the $_POST when you use prepared statements. It does it automatically for you.

Also, you should read up on this when you are trying to validate inputs. This is a good example of why you shouldn’t rely on isset($_POST[‘’])

http://stackoverflow.com/questions/10943060/isset-postsubmit-vs-serverrequest-method-post/10943179#10943179

Wow, my example is horrible. I meant “shouldn’t”, not “should”. Here’s the proper one.

$stmt = $mysqli->prepare("UPDATE new-equip SET itemName = ?, model = ?, serial = ?, desc = ?, WHERE itemID = ?");
$stmt->bind_param('ssssi', $itemName, $model, $serial, $desc, $itemID);
$itemName = $_POST['itemName'];
$model = $_POST['model'];
$serial = $_POST['serial'];
$desc = $_POST['desc'];
$itemID = $_POST['itemID'];
$stmt->execute();
$stmt->close();

Missing semi-colons.

Still wrong - in fact far worse. You should VALIDATE the $_POST variables before copying them to another variable. If you don’t then there is NO POINT in copying them - you’d be better off using the $_POST names so that you know they can still contain JUNK/GARBAGE. and that there is no guarantee that they will not result in injection into whatever you are going to use the fields for when you read them back from the database.

No no no. You’re getting it wrong. I haven’t actually attempted to help the OP yet. I just wanted to correct his post. He was stuffing the actual $_POST values inside the query which is a wrong attempt. If you said my attempt to modify OP’s code is far worst, then I don’t know how worst is OP’s original code because stuffing actual $_POST values inside queries are far far worst then my attempt.

I couldn’t edit my first post so that’s why I had to post again to clarify what I was trying to show OP. I know that you should validate inputs, I’m not that slow. Don’t think for a minute that I’m as slow as you think I am. I couldn’t edit my post so I had to just re-post what I was trying to get at.

Taking the post value, putting it into a local variable, and putting it into the query is no different than putting the post value directly into the query.

OP: MySQL does not have a type “longbow”, so i’d suggest double-checking your field type. If it’s a LONGBLOB, you’ll need to send it as blob data, as Jeff pointed out.

Your code is effectively identical to that of the OP except that with theirs it is obvious that the fields passed to the SQL have not been validated whereas you need to read the rest of the code in order to tell that your variables also haven’t been validated. With your code there is no way to tell by looking at the code whether the variables are valid or JUNK.

You are converting potentially untainted variable names into tainted ones - by copying the $_POST values to another name you now have no way to tell what variables are valid without actually testing the value again or examining ALL of the code and testing all of the possible paths through it.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.