Pretty soon I'm going to do a bunch of stuff regarding SQL injections and PHP. I'll post a blog about it on here once I get to it. Anybody done any work on how to safeguard against SQL injection in PHP?
This is a great topic. There are tons of ways to achieve protection from SQL injections. From my experience, one of the simplilest ways to do this is simply escaping characters in user supplied fields. For example, here is a classic case of MySQL Injection:
SELECT * FROM `USERS` WHERE USERNAME='$username'
This doesn't seem harmful if used properly. If I put in my username of Kevin the query will execute as:
SELECT * FROM `USERS` WHERE USERNAME='Kevin'
However, if I want to be an ass, I could inject SQL code by entering my username as ' OR 1 OR USERNAME='
Then the query will look like:
SELECT * FROM `USERS` WHERE USERNAME='' OR 1 OR USERNAME=''
Now the query reads, select all data from users where username is blank OR if 1 == 1. Well, this query will always be true, and return all records for all users. This could be really bad!!!!! A simple string replace of characters (such as ', #, \) can prevent this problem:
SELECT * FROM `USERS` WHERE USERNAME='\' OR 1 OR USERNAME=\''
Now, the query will seek a user with the name of 'OR 1 OR USERNAME='
The best way I've found to prevent this type, or any type, of SQL injection in PHP is to use prepared statements. That basically means we're taking the SQL statement and parsing it on the database server seperately from any parameters.
I feel a blog post coming on!
I don't get this, how will an external user have access to typing this query? And how does he have the user rights to search the database anyway?
The rights are what we're trying to protect.
SQL injections, in my opinion, is a lot of trial and error. If you have a form that doesn't "clean" the input a mean person could essentially put SQL into the form that is passed to the database and return a result. Example:
Let's say you have a form on your website that allows users to search for other users by first name. That form would have an input box where you could put your first name. Odds are that form is going to take the text you put in the box and use it to query a table, or many tables, to find the relevant info. So you input the name "Dave" and the HTML form passes back to PHP that word. Then PHP queries the database with something like this:
$fname = $_GET['fname'}; $query = sprintf('SELECT * FROM people WHERE fname = %s',$fname);
If you wrote "Dave" as the name the query would search for everything where the fname = Dave. Now if you had a mean person try to do SQL injection they wouldn't search for "Dave", they'd search for "whatever; SELECT password FROM people ORDER BY fname;" Then the query would look like this:
SELECT * FROM people WHERE fname = whatever; SELECT password FROM people ORDER BY fname;
This would return everyone's password along with the origional query. Now that wouldn't do much because the table output is formated specifucally so the passwords wouldn't be returned. But now you know there is at lease 1 Dave in the system...so you could do
SELECT * FROM people WHERE fname = 'whatever'; UPDATE people SET password = 'my_new_hacked_password' WHERE fname = 'Dave';
Now using SQL injection the mean person just changed every Dave's password to one of their choosing. Then they could troll the forums to find a Dave's username and BAM! They've got themselves unauthorized access on another person's account. Not good
The person doing the hacking wouldn't have rights, but PHP would. SQL injection is taking advantage of queries being static, but using user provided content. That's why you should pass the form elements through some sort of filter to find malicious elements and remove them before that string is passed to the database with the query.
I hope that clears it up a bit.
What about instead of having the page directly access the database for accounts, have it access the database via an external file that just keeps the usernames for the sake of search?
The input is still what is vulnerable in SQL injections. At some point, external or not, a string is passed to the query for use in searching the database. SQL injection exploits that connection that has to happen at some point. All that you need to do though is filter the input text.
I like this stuff, hope you will make a php tutorial too.
I would like to, but a full fledgd PHP tutorial wont happen for a while. We're talking about doing some 1-off mini-tutorials after the core 7 are completed. Those will cover a very specific thing in like 5 minutes or less. We may cover some PHP stuff in those.
How about having two different databases, one for search with no sensitive data, and one for the passwords?
It is much easier to simply clean the strings that are being passed to the database. Your making a mountain out of a mole hill here.
I think you're also only seeing the exmaple I provided. There are other dangers besides posting a new password. A mean person could add or drop tables, or perform any other action the database user is allowed.
For most web applications your database account tied to your application will, at minimum, need to be able to drop and alter tables. So even if you split your database so every single table lives in one it's own server (which would be a nightmare to set up and maintain) you still pass strings to a database with permissions to perform actions on that database. So instead of
UPDATE people SET password = 'whatever' WHERE fname = 'Dave";
Since now you've put your password in a seperate table or even database the mean person could simply do
DROP TABLE people;
And now instead of a compromised user base you have no user base.....put you'll have passwords.
Welcome to the WiBit.net forums! Check out our terms and features:
Annoy your friends
Is this something you think someone else would enjoy? Is it not for you, but do you know someone who is down with this type of content? Well share away:
This blog is a reminder that cheating in software development can get you into big trouble. Sometimes developers get really really lazy, OR are pressured to write something using overly simplified data structures. Almost every time this happens you are bitten in the butt! Sometimes the problems show up immediately and other times it may take months or years (especially in integrated systems).
- 1 of 78