As a web developer, you need to be on the lookout for many different types of security issues, and SQL injection is a major one. SQL injection is a technique where malicious MySQL statements are inserted into your SQL. Learn more about what SQL injections are and how they work in this online training video.
- View Offline
My SQL has a very definite syntax, and we've been learning that. A few times in the last movies, we even broke that syntax on purpose just so we could see what the error messages looked like. Once we start constructing SQL queries using dynamic data from variables, then we also have to be careful that the values we use don't break MySQL syntax. For example, the single quote is an important part of an insert statement because it goes around all string values. So for example if we had insert into subjects, we have the column names values and then about widget corp and that goes in single quotes. Now let's imagine that we've written that so that it takes a dynamic value. We did this just a few movies ago.
Where we're going to now insert menu name and menu name is going to be a variable. Well what if our menu name is Today's Widget Trivia? What if that's the string that we want to drop in there? Insert into subjects, menu name position visible, the values Today's Widget Trivia. Do you see the problem with that? Let me highlight it for you. We're closing our single quotes without meaning to. The result of this is that MySQL thinks that the string that we're sending is "today," and that's it, and we have broken the rest of it. Everything else after that will be seen as being garbage and we'll get an error back.
Now, this is an innocent example, but sometimes the values that come in are not ours, nor are they even from well-meaning admins of the site. URL strings, form data and cookies are often coming in from the public at large. And therefore they're completely out of our control as developers. And not everyone who comes to our website has our best interests in mind. If we use those values exactly as they come in we could be in for a world of hurt. Let me show you an example. Let's say that we have menu name and it's equal to that single quote at the beginning followed by some SQL that someone else would like us to run. Followed by another single quote at the end which they may have to modify it slightly so that it doesn't raise an error and it actually does execute. But you can see the result here.
They're basically taking what was a simple insert statement and turning it into dropping our entire table of subjects. And they can do other things, too. They can actually have it export all of our users and their passwords, things like that, that we don't want them to do. This process is called SQL injection. The user sends a carefully crafted URL string, or a form field value, and it injects their SQL into ours. SQL injection is the single easiest way for someone to hack your website and steal your data. Sql_injection is the single biggest problem that you need to be guarding against as a web developer.
There are lots of things that you need to watch out for when you are developing for the web. And lots of security issues you should be concerned about. But sql_injection is the big one. Now if you stop and think about it. Breaking the syntax of SQL is similar to how we saw that we could break the syntax of a URL or HTML earlier on. And the solution, here, is going to be the same as it was for both of those. We need to escape the string. That is, to transform it so that any problem characters that are in it are rendered harmless. So, let's learn how to do that. Let's learn the ways in PHP that we can escape strings to make them safe for putting into queries that we're going to send to SQL.
- What is PHP?
- Installing and configuring PHP and MySQL
- Exploring data types
- Controlling code with logical expressions and loops
- Using PHP's built-in functions
- Writing custom functions
- Building dynamic webpages
- Working with forms and form data
- Using cookies and sessions to store data
- Connecting to MySQL with PHP
- Creating and editing database records
- Building a content management system
- Adding user authentication