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 with text input. The recommended way is to use a prepared statement, which is the subject of the next chapter. The other way is to use the PDO quote method, which wraps a value in quotes and escapes special characters. Let's take a quick look at how the quote method is used. This is pdo_quote.php, which you can find in the chapter two, 02_09 folder of the exercise files.
On line 6, a variable from the form is embedded directly in the SQL query. Although it's wrapped in quotes, this is potentially dangerous. The query uses the LIKE operator and also it's got these percentage signs as wildcard characters on either side of the search term. Because the quote method wraps the value in quotes, we need to add those percentage characters to the search term before it's passed to the quote method.
So let's create a new line on line 5, and we'll create a variable called searchterm. And then we will assign to it the wildcard characters, the percentage signs, and concatenate onto that the search term, the variable which is coming from the form, and then the final wildcard character at the end. So that's now ready to pass to the quote method. We can clean up the end of the SQL statement, so let's just move it like that so we've got LIKE and then we need to concatenate the value that has been brought from the form after it's been sanitized by the quote method.
The quote method is on the database object, so that's db, then quote, and we pass it the value of searchterm. This will escape any special characters that have come from the form and it will wrap everything in quotes, so it'll be safe to put inside that SQL query. So if we just save that, we can then go back to the browser, and we can test this. We can put in some names. I don't know if we've got Thomas in there. No results found.
Let's try a. Lots of results found there. So now the form input is being filtered using the quote method. We're protected from SQL injection. Using the quote method to sanitize user input is very easy, but it's not considered best practice for a variety of reasons. It's not supported by all PDO drivers, notably ODBC, making code that uses quote less portable. Its output is dependent on the database's character set, which must be set either on the database server directly or in the data source name.
The quote method ignores changes to the character set made through an SQL query. Using quote is often slower than using a prepared statement. In view of these disadvantages, when using PDO, you should always use a prepared statement rather than quote to embed user input. We'll look at how to use prepared statements in the next chapter.
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.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.