Viewers: in countries Watching now:
Now that PHP has true object-oriented capabilities, it's best practice to access databases using PDO (PHP Data Objects) and MySQLi. These methods produce database-neutral code that works with over a dozen systems, including MySQL, SQL Server, PostgreSQL, and SQLite. Learn how to use PDO and MySQLi to perform basic select, insert, update, and delete operations; improve security with prepared statements; and use transactions to execute multiple queries simultaneously. Author David Powers also covers advanced topics like instantiating custom objects, and compares PDO to MySQLi so you can decide which method is right for you.
With an online form it's important to sanitize user input to protect your database against SQL injection attacks. There are two ways to do this. The reccomended way is to use a prepared statement and that's the subject of the next chapter. The other way is to use the MySQLi real escape string method which escapes special characters, making them safe to embed in SQL. So let's take a look at the code for this page. It's mysqli_escape.php which you can find the chapter five 05_08 folder of the exercise files.
On line six, a value from the GET array, GET searchterm, has been embedded directly in the select query and this is potentially dangerous. To prevent SQL injection, values that come from external sources such as the GET array need to be sanitized by passing them through to the real escape string method if you plan to embed them in an SQL query. You call the method on the database connection object. So to make this query safe, we need to pass GET searchterm as an argument to the method.
So let's do that. So add a new line in there. Make a bit more space. And then the database connection object, DB, and call it's real_escape_string method. And we just parse it get search term as an argument. And that's sanitizes it. The real_escape_string method doesn't wrap the value in quotes, it only escapes potentially dangerous characters. So we still need the original quotes from the query. So if we save that and go to the browser we can test our page.
Let's see if we find any names beginning with i-s. Yes, Isabella means devoted to God. Let's see if I'm in there. I don't think so. No results found. So, the SQL query is now working perfectly. More importantly, the database is being protected from SQL injection attacks. In this example, I've used the real_escape_string method in the middle of an SQL query. This can make it difficult to read, particularly if you've got a lot of variables that you're embedding in a query.
And if I'm using real_escape_string, what I like to do is to assign the value to a variable, and then use the variable in the SQL. What I'm going to do is I'm going to cut this from here and then before the SQL. I'm going to create a variable called searchterm and then I'm going to assign the value of GET searchterm after its been passed to the real_escape_string method. So I can now rewrite this SQL and embed searchterm directly in it. So what I'm going to do I'm going to change from single quotes to double quotes, and then around searchterm we will need single quotes. Begins with the wild card character, that percentage sign, and then we need search term and then we just have the wild card character at the end of it, and we need to change that to a single quote, and then a double quote at the end. That has completely rewritten it, but I think that, that makes it much easier to read the SQL query like that.
So, let's just save that and test it. It should work exactly the same as before. So, we'll search for names with j in them. There it is. It's working fine. So that's the way in which you sanitize user input using the real escape string method. In the next chapter, we'll see how MySQLi prepared statements handle user input automatically by escaping potentially dangerous characters and adding quotes around values wherever necessary.
There are currently no FAQs about Accessing Databases with Object-Oriented PHP.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.