Your Binary Data Isn’t Safe to Insert…Yet PHP Help

The code you built in the previous section looks good, but if you run this code, you’re likely to see some errors. First, that binary data has all sorts of weird characters on which PHP and MySOL are going to choke. There’s always the possibility of running into characters that are a problem, but it’s especially true when you’re dealing with binary data.

Once a.gain, though, there’s a utility function for that.

The mysql real_ escape_string function escapes any special characters in the string you hand it. This means that you can pass in your $image_data, and then pass the result of mysql_ real_escape _string to mysql_ query through your INSERT statement. In fact, it’s not a bad idea to use this function on any string data you pass in to MySQL:

Your Binary Data Isn't Safe to Insert. ..Yet

Printing a String to a Variable

As natural as this code looks, it’s got a serious problem. Even though the curly braces surrounding a variable will allow that variable to be printed inside a string (for example, “{$variable}” prints the value of $variable), PHP draws the line at doing actual work inside the curly braces. As such, it won’t interpret the call to mysql_real_escape_string.

You have two ways to get around this. The first is the easiest: you could just move the calls to mysql_ real_escape _string up into the variable assignments, sort of like this:

// Insert the image into the images table
$image  $_FILES[$image_fieldname];
$image_filename = mysql_real_escape_string($image[‘name’])j
$image_info ” getimagesize($image[‘tmp_name’]);
$image_mime_type = mysql_real_escape_string($image_info[‘mime’])j
// and so on….

This also looks OK, but it’s not a good idea. Do you see why?

Think about the function you’re calling: it’s specifically for getting values set up to work with MySQL. However, what if you want to use $image _file name somewhere else in your script? You’ve turned this variable into a MySQL-specific Version of the file name.

It seems like the original approach-converting the variable by using mysql Real_ escape_string as it’s going into the actual SOL INSERT statement-is the right one. It allows the variable to just be the image file name, or the image MIME type, and then you convert that into a MySqL-friendly value when that’s required,

That seems to indicate there’s a need for a way to perform calculations or run functions on values when you’re constructing your SOL string-and there is. You usually do so by using sprintf, which is a PHP function that prints to a string. In other words, you construct a string by using any calculations you need and pass all the required information to sprintf. The sprintf function puts everything together and returns a string, which you can then assign to your variable, and boom, you’re then ready to pass that variable in to mysql_ query.

How does this work? Well, it’s a little different than anything you’ve done so far. Instead of just building the string up via concatenation, you indicate the entire string that you want to create, but every time you come to a spot in the string where you want to include the value of a variable, you put in a special type specific For example, you use % for a string type:

$hello = sprintf(“Hello there, %5 %5”, $first_name, $last_name); echo $hello;

Suppose $first_name is “John” and $last_name is “Wayne.” Running a script with these two lines would give you:

Hello there, John Wayne

The sprintf function replaces the first %s with the first value after the string, which is $ first_ name.Then, it replaces the second %s with the second value after the string, $last_ name Finally, the entire string with the values inserted-is assigned to $hello.

What’s great about sprintf is that you can perform calculations on variables before you pass them to sprintf. The following example might be a bit silly, but the code” is perfectly legal:

$hello = sprintf(“Hello there, %5”, $first_name . ‘ , . $last_name); echo $hello;

Of course, there are much better ways to use sprintf, like creating a query string and using mysql real_ escape_string in the process:

// This replaces the older assignment to $insert_sql
$insert_sql = sprintf(“INSERT INTO users ” ,
“(first_name, last_name, email, ”
“bio, facebook_url, twitter_handle) ”
“VALUES (‘%s ” ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’);”,
// Insert the user into the database
or die(mysql_error(»;

This code doesn’t do anything noticeably different than your older version. This is because the data being inserted into users was probably not a problem in the first place. But now, you can take this same approach and apply it to your insertion into images.

$insert_image_sql = sprintf(“INSERT INTO images ”
“(filename, mime_type, ”
“file_size, image_data) ”
“VALUES (‘%s’, ‘%s’, %s, ‘%s’);”,
or die(mysql_error

You can guess what %d means to sprintf: replace that type specifier with a decimal number, like 1024 or 92048. Thus, this code builds up an INSERT, executes it, and escapes your values in the process.

sprintf Is Your New Best Friend

Most PHP programmers use sprintf initially because it lets them do things like use mysql_real_escape_string on variables before they’re inserted into a query string, But those same programmers discover something else, just as you will: using sprintf lets you write a lot more robust and flexible code.

Using sprintf. you can do calculations on your data, escape values, and do just about anything else you want to your data, as you’re inserting into or selecting from your database, You no longer need to calculate things and then assign the results of those calculations to a variable (or, even worse, a new variable, based upon some old variable) and then-and only then-use those variables as part of a SQL construction.

sprintf lets you do all that in a single step. In general, you should use sprintf as your default means of creating SQL strings that are executed as queries against your database.

Now, try this out. Head over to create_user.php once again, find a new friend to fill out the form, let her choose an image, and then submit the form. Your new version should run, and you’ll get to This time you see the user’s profile, because that’s not code you’ve written. In fact, you might see an entirely incorrect user being loaded. You’ll fix that soon.

You should, however, be able to dig into your new images table and see an entry for the uploaded image:

Printing a String to a Variable

You can also access your table by using phpMyAdmin (see the box on page 55) if you’ve got that running, and extract a little extra information about your entries in images Figure 10-1 shows you what to expect.



Posted on January 12, 2016 in Binary Objects and Image Loading

Share the Story

Back to Top