5a.3. Welcome to SQL injection Break Neck has fallen victim to a SQL injection attack, and now all their customers are paying for it. Remember Joe? He's the JavaScript guy at Break Neck. So how did this... This doesn't make a lot of sense, but it sure looks harmless. Weird... but harmless. ...somehow result in this? This sure isn't harmless, though... it's every customer in the Break Neck database! brain power What do you think happened? How could you prevent people from getting access to all the Break Neck customers' names and addresses? |
SQL Inspector To really understand what's going on, you're going to have to get into a little SQL. Below is part of the lookupCustomer.php script that is requested b getCustomerInfo() when a phone number is entered into the Break neck form... we've highlighted the line of SQL that asks the Break Neck server for a customer based on the phone number entered into the web form. if (!$conn) die("Error connecting to MySQL: " . mysql_error()); if (!mysql_select_db("headfirst", $conn)) die("Error selecting Head First database: " . mysql_error()); $phone = preg_replace("/[\. \(\)\-]/", "", $_REQUEST['phone']); $select = "SELECT *"; $from = " FROM hraj_breakneck"; $where = " WHERE phone = '" . $phone . "'"; Here's lookupCustomer.php, which you first saw back in Chapter 2. $queryResult = @mysql_query($select . $from . $where); if (!$queryResult) die('Error retrieving customer from the database.');
Your job is to take this query, and then write in the special characters that PROJECT: CHAOS asked you to type into the Break Neck web form. Then, you'll know exactly what's being sent to the Break Neck database... and maybe even get some ideas about what's going on. Here's the SQL that lookupCustomer.php creates. SELECT * FROM hraj_breakneck WHERE phone = ' ' Write in what you typed into the phone number field here.
|
Wait, I think I see what's going on here... Frank is the lead PHP programmer at Break Neck. Here's what the SQL statement sent to the Break Neck database becomes... SELECT * FROM hraj_breakneck WHERE phone = ' ' ' || 'a' = 'a Let's make that a little easier to read...
SELECT * FROM hraj_breakneck Notice how the single quotes you entered into the phone field work with the quotes already in your SQL query. WHERE phone = '' || 'a' = 'a' This "||" means "OR" to a SQL database. ...which is the same as saying this...
SELECT * FROM hraj_breakneck WHERE phone = '' This will return false for all customers, since they all have a phone number. OR 'a' = 'a' Uh oh... this is always true! 'a' is always equal to 'a'.
Here's the problem! This SQL query will be true for every customer! Team Chat: SQL Injection Team Chat: SQL Injection So no matter what the customer's phone number is, the WHERE clause is going to return true... ...because of that part about 'a' = 'a', right? That's always a true statement. Exactly. So the query returns all the customers in the database, instead of just the one with a specific phone number. I can see why everyone got upset! So how do we fix this? We're getting like 10 complaints a day. Well, I was reading about this thing called "magic quotes" in PHP. I think if we turn that on, it will protect us against SQL injection attacks. Actually, Frank, magic quotes is going away in PHP 6, and a lot of people don't like that feature. It's a better idea to not count on features like that... take control yourself. Hey, I saw some JavaScript code the other day that validates a phone number. I bet I could add that to the Break Neck form, and help avoid this happening... ...and aren't there some MySQL functions I can use to make sure that this doesn't happen? I guess that would be better than counting on some sort of "magic quotes" function to help me out. I'll bet we can fix this... Yup. Let's get to work. I'll take care of a little validation code, and you add that MySQL thingie you were talking about... ...and we can get back to the business of happy customers and hot pizza. |
Wait a second... so this is really a server-side problem? Why should I have to write more JavaScript if I didn't cause the problem in the first place? Validation protects your web app from hackers. You should always validate user input. It's still your web application Sure, SQL injection affects people like Frank and Anne, who work with the Break Neck PHP, more than it does the asynchronous JavaScript programmers. But if something goes wrong, you better believe that you're still going to hear about it! Besides, with just a little bit of extra JavaScript, you can really help make your app more secure. Validation protects everyone, not just the server-side guys. Frequently asked questions? | Q: | I'm totally lost on all this PHP stuff. I thought this was a book on asynchronous programmg! | | A: | It's OK if you don't really understand PHP. In fact, lots of programmers handle the JavaScript code on a web app, and then work with an entirely different group of people for the server-side part of the application. The main thing you need to know is that things can go wrong on the server. | | Q: | So if I don't understand what's going on, what's the point of knowing about SQL injection in the first place? | | A: | All you need to know is that things can go wrong... and that most of the time, things will go wrong. Now, you can talk your server-side guys, and help make sure nobody is getting your customer's addresses or phone numbers. | | Q: | Then this is really all about communication, right? Between me and the other people on the team? | | A: | Exactly! There are plenty of times you won't know the answer to a problem, or even that a problem exists. But if you just spend a little time with the other folks on your team, your web app will be much the better for it. Along the way, you might even pick up some ideas about making your code better... like validating input... |
|
|
