Escape Strings For MySQL To Avoid SQL Injection

Thanks For Sharing! :-)Share on Google+Tweet about this on TwitterShare on FacebookShare on RedditBuffer this page

Escape Strings For MySQL To Avoid SQL Injection

With all the talk about working with databases using MySQL and PHP in this tutorial series, one thing we didn’t cover yet is SQL Injection and how to protect your site from it. The syntax for MySQL is very specific, and if you don’t get it right, it is easy to break. As we build up queries using dynamic data from our variables in PHP, you need to be careful that any data contained in those variables do not break the syntax as well. One of the main things to look out for is the single quote in strings. In this episode we’ll talk a little bit about SQL Injection, and the method used to combat it.


What Is MySQL Injection

SQL Injection is the process of a malicious hacker on the internets that purposely tries to take advantage of the specific nature of SQL syntax, and the fact that it can be broken. If a hacker is able to carefully put together an URL string, form data, or cookie data, to nefariously inject their malicious SQL into yours, your database could become the victim of dropped tables, stolen data, entire databases being dropped, or worse. The main idea is that the hacker takes advantage of the ability of single quotes to denote starting and ending points of SQL code. If those single quotes are not properly escaped, then they are prone to this type of attack.


The History of Escaping Strings

The problem of escaping strings goes all the way back to the beginnings of PHP. Recall the main problem is that pesky single quote. There may be times when a string contains a literal single quote that is needed, but we need to make sure that MySQL understands that this particular single quote is not the end of string boundary, but an actual character that we want in the string.

Problem

If we were inserting this into our database, it might look something like this:

The problem is that the single quote included in the string may cause a problem for MySQL.

Solution

The solution to this problem is to simply escape the string like so.

What Does It Mean To Escape A String?

This is simply a means of telling MySQL that this is not the single quote that ends the string, rather it is part of the actual string itself and should be treated as such. So as you can see the way that we assign this special meaning to the character so that MySQL knows it is safe, is to prepend it with a backslash character. PHP has had a few ways to try and deal with this over the years, let’s look at a few now.


An Escaping Strings History Lesson

If you have a lot of data in your PHP application, you can see that having to escape any single quote that may exist in the strings of your application by hand would be a tiresome chore. Not only that, you’re bound to miss a few and introduce problems despite your best efforts. Wouldn’t it be ideal to simply have a function that does this for you? Of course it would, so PHP added this ability to the language many moons ago.

addslashes($string)

It all started with the addslashes function some time in the past. This function takes a string as it’s argument, and returns the string with any problem characters like a single quote automatically escaped for you. This was a good idea, so good in fact, that it was made a default baked into the language by way of something called Magic Quotes.

Magic Quotes

Magic Quotes is a configuration directive in PHP that would automatically call addslashes on all GET, POST, or COOKIE data by default. The thinking was that this would save developers the mistake of forgetting to do this on their own and open up their websites to a security vulnerability. This was added in PHP2 and became the default in PHP3. All good things come to an end however, and in PHP5.4 Magic Quotes were sent to the trash can. Why you say? Well, it caused a lot of confusion for developers, and made programs much less portable from host to host. This is because one never knew if the configuration was on or off for Magic Quotes. So in some cases, the code would work just fine, and in others if might fail altogether. There is a solution to these woes however.

mysqli_real_escape_string( $connection , $string )

In the wonderful world of PHP, we sometimes run across insanely long function names with underscores in between the words. This is one such instance. This is a great function however which you should make use of. It takes a string and then escapes it in such a way as to make it perfectly safe for MySQL statements. Therefore, you can build up your queries in PHP as much as you like, then before you run the query, just make sure to put the fully assembled sting through mysqli_real_escape_string and all will be well.

Observe

Note that when the query is first echoed out, it contains all of those problematic ‘ characters. In MySQL, strings must be enclosed by single quotes exclusively, so by putting this string inside of single quotes, the query is now broken and dangerous to the database. Also notice that once we run our query string through the mysqli_real_escape_string function, it comes out crisp, clean, and safely escaped for use with the database.


This Is Not Good
Isn’t it nice that we don’t have to escape ‘ characters all by ourselves?


This Is Good!
Isn\’t it nice that we don\’t have to escape \’ characters all by ourselves?


Provide The Correct Arguments

The mysqli_real_escape_string function takes two arguments. The first argument is the database connection itself, and the second is the string you want to cleanse. It’s pretty obvious that we need to provide the string to clean, but the database connection is not as obvious. Just remember to provide it, or the function will in fact fail.

The mysqli_real_escape_string Conclusion

The takeaway from this quick lesson is, keep your data safe by properly using the mysqli_real_escape_string function. When you’re using a framework, you won’t need to worry about it, but when native PHP and MySQL is in use, you need to take care of this step manually.

Thank you for reading Escape Strings For MySQL To Avoid SQL InjectionPlease do share using the buttons below!

Thanks For Sharing! :-)Share on Google+Tweet about this on TwitterShare on FacebookShare on RedditBuffer this page

4 Components Of CRUD With Native PHP and MySQL

Thanks For Sharing! :-)Share on Google+Tweet about this on TwitterShare on FacebookShare on RedditBuffer this page

4 Components Of CRUD With Native PHP and MySQL

In the last lesson we learned more about working with results from select queries in MySQL. In other words, when we used PHP to build up a query and send it to MySQL, MySQL then sent back a resource which could be massaged by any of the four techniques we discussed to get access to the data contained in the resource. So of all the MySQL commands such as SELECT, INSERT, UPDATE, and DELETE, SELECT is the only one to return a resource on success which requires further processing. The other commands simply return either true or false on a successful query or failed query respectively. In this episode, we’ll continue working with the database and build on the prior lessons concepts. We’ll now start completing inserts, updates, and deletes as well. Let’s get to it!


What Does A Query Return?

First off, let’s take a look at the different scenarios for what we can expect when actually executing the query against MySQL. A query is either going to succeed or fail, and the table here shows each of the possible 8 scenarios you may have.

  Query Success Query Failure
SELECT resource false
INSERT true false
UPDATE true false
DELETE true false

What this tells us is that the SELECT is going to strictly follow the 5 step process which is to:

  • 1. Create The Database Connection.
  • 2. Perform A Query.
  • 3. Make Use of Data Retrieved.
  • 4. Release Memory.
  • 5. Close The Connection.

All other cases for INSERT, UPDATE, and DELETE, are only going to require 3 steps. Those would be 1, 2, and 5 like so:

  • 1. Create The Database Connection.
  • 2. Perform A Query.
  • 5. Close The Connection.

How Do You MySQL Insert With PHP?

With this knowledge, lets create an insert statement into a database table. We’ll use a links table, here is the sql to create it:

Slick! This is a good example of being able to open one database connection at the top of the page, run multiple queries as needed in the body of the page, then close the database connection at the very end. In this example, ran this page two times. Once with the variable values of $url = 'http://yahoo.com'; and $name = 'Yahoo'; while the second query contained $url = 'http://vegibit.com'; and $name = 'VegiBit';. Once the connection was opened, we first did a query using the INSERT statement to put bookmark into our links table. Further down the page, but before the database connection was closed, we ran another query using the SELECT statement to immediately read back the data we just inserted. Once we did all of this, we simply closed the database connection.

mysqli_insert_id

Typically when doing inserts into a MySQL database, you’ll have an ID field that auto increments on each new insert. You never specify the value of this field, MySQL does it all automatically for you. The problem however, is that this field is also typically the unique identifier for a specific row or record of data in the table. How often do you hear something like, find by ID? If you are entering new records into the database, and you’re not specifying what their ID is, then how in the world will you find out this information? You’ll find this out with the mysqli_insert_id() function, and it is a critical function to be aware of. Maybe you need to do another insert into another table right after the first insert, and you need to use the prior ID for this purpose. This happens all the time in relational databases. The function signature is like so:

$id = mysqli_insert_id( $connection );

This function returns the id of the most recently inserted record on the given connection.


How Do You MySQL Update With PHP?

Now that we completed some inserts using PHP and MySQL, we should take a look at how to update records in the database. There are a few things to be aware of when doing updates. Usually, you’ll want to complete updates by the record’s unique ID. When doing an insert, we don’t need to worry about that since MySQL generates an ID automatically with an auto increment function. In this example, we’re just going to look in the database and choose the ID that we would like to update. In the real world, this ID will have been generated on a different page. The other page will have used a SELECT to pull the record out of the database, then it would be displayed on that page along with the ID of that record. Now that ID may or may not be visible to the user on the page. Most likely, it is transparent to the user, they wouldn’t even see it. Now there might be an ‘Edit This Entry’ type of link that would essentially pass along the ID via $_GET when you click that link. There may also be a form which has a hidden field pre populated with the ID and get passed via $_POST. The main idea however, is that one way or another, you are going to need to provide that ID in order to update it! Let’s do it.

Note! The query worked perfect and we can see that Google is now The Google and 1 row was affected.

$count = mysqli_affected_rows( $connection );

That mysqli_affected_rows function is going to come in handy, and is often used with update and delete statements in MySQL. The reason is, you may run an update query with success, but no rows were affected in the database. What this means is that, yes you sent a query, but there was nothing to change for that particular ID, so it did not affect the row. To test this, we can run the query above multiple times and it is only the first time that 1 row gets affected. All subsequent queries that use the same query string are successful, but no rows get affected. This is important to keep in mind.


How Do You MySQL Delete With PHP?

We now know how to Createinsert into table (column1, column2, column3…) values (value1, value2, value3…)” records in MySQL with PHP, Readselect from table where expression” records from MySQL with PHP, Updateupdate table set column1=value1, column2=value2 where expression” records in MySQL with PHP, and now we’ll learn how to Deletedelete from table where expression” records in MySQL using PHP. Create Read Update Delete. These are the four components of CRUD, and with them, you are now free to rule the world. Let’s complete our training with the delete.

Awesome! Note that the query worked perfectly and the entry associated with ID 11 is gone, the bookmark link for Google.

Conclusion

We’ve come full circle covering the basics of PHP and MySQL manipulation. We can see it’s a pretty straight forward process to implement CRUD with native PHP and MySQL. Now, using any of the awesome PHP frameworks to handle these chores for you is certainly an option. In cases where you might have a client that has hosting that might not support the latest and greatest technologies however, you’ll need to know how to do these things by hand in PHP. For those scenarios, this PHP Tutorial Series is the answer to your woes.

Thank you for reading 4 Components Of CRUD With Native PHP and MySQLPlease do share using the buttons below!

Thanks For Sharing! :-)Share on Google+Tweet about this on TwitterShare on FacebookShare on RedditBuffer this page