Forum Moderators: coopster
MySQL is entirely safe and happy with:
SELECT * FROM `users` WHERE `username` = 'username' AND `password` = 'DELETE FROM users'The danger comes when you allow the user input (DELETE FROM `users`) to include delimiting characters, in this case '
Using [1' OR 1 OR '1] will guarantee the right password:
SELECT * FROM `users` WHERE `username` = 'username' AND `password` = '1' OR 1 OR '1' Logically, because you were able to terminate the delimiter around the password by including ', you are able to inject additional logic to the SQL statement.
The solution is simple: mysql_real_escape_string($password) first:
$password="1' OR 1 OR '1";
$password_safe=mysql_real_escape_string($password);
print $password_safe;In the SQL statement this becomes:
SELECT * FROM `users` WHERE `username` = 'username' AND `password` = '1\' OR 1 OR \'1' Only users with a password actually equal to [1' OR 1 OR '1] will be able to log in with that password!
So, drop the REGEX idea, and learn about mysql_escape_string() for your strings.
For numbers (user IDs, etc.) then you should be using intval() or floatval() to ensure that the return is only a number (no string can be returned from either of those functions).
mysql_real_escape_string is absolute guard against attacks with same type
send dirty request to db
or throw away it after parsing by script
So you can test all of your posted fields to see if they contain what you expect. However if you allow random text then you would need to allow people to use the ', # and ; characters. So all of these could cause you trouble, so would annoy people if they keep getting a message telling them they cant use there punctuation.
You really are going about this in a strange way - the things you are worried about are the wrong things. There are lots of things to be worried about, but not those.
It is also dangerous to keep writing them here... someone might use your ideas and end up with insecure code which can be hacked into more easily.
Here's a simple example:
If you say "I don't allow "DELETE" in my statements...
$sql=str_replace("DELETE","",$sql);
Start with:
$sql="DELDELETEETE FROM `users`";
Run the str_replace... end up with:
DELETE FROM `users`
What I mean to say is that you are opening up more and more security holes with your approach.
>>> For space economy, use a compressed file system.
it's non-good way: speed of the db will be low very much.
As vince said you seem to be making your life a lot harder than it needs to be. There are specific functions in the php library that will assist with stopping sql injection, like mysql_real_escape_string, you can of course make your own function that does a similar thing.
You can use a regex to limit what is allowed in each filed. This wont work if you are going to allow people to enter text, as you need to allow words like delete and punctuation that could cause you a problem.
So why do you not want to use mysql_real_escape_string?
If speed is a problem then get a better host. If the one you are using cant provide the cpu power, ram or disk space then get a better one, simple as that.
[1][[b]edited by[/b]: SarK0Y at 2:28 pm (utc) on Nov. 28, 2008][/1]
If you are checking $szUserName against the DB, you use the function on each string before inserting to your query, so...
$szQuery = "SELECT * FROM tblUsers WHERE `userName` = '" . mysql_real_escape_string($szUserName) . "'";
Since we are affecting only the Variable before we write to the query, the quotes in the actual query will still be correct. IE, if $szUserName is [ '; DELETE * FROM tblUsers;-- ] then the query will be:
SELECT * FROM tblUsers WHERE `userName` = '\'; DELETE * FROM tblUsers;--';
However, if you use mysql_real_escape_string on $szQuery instead of $szUserName, your query would be:
SELECT * FROM tblUsers WHERE `userName` = \'\'; DELETE * FROM tblUsers;--\';
which would be the incorrect query. I believe you are thinking like the second option, but I'm not sure; use the function only on the variables for the query and it will work without having to decode.
Your method works the same way as the mysql_real_escape_string, except it may break! What if I am inserting "I need two of them! 1 for tea! 1 for crumpets" but I typo and create "I need two of them!1 for team!1 for crumpets!". Instead of getting the typos, the decoding script replaces them with quotes!
You could also use addslashes, which would escape ', ", and [NULL] for your DB queries. This one does not require a database connection and will not require decoding the characters on output. Can someone else comment on whether or not addslashes is sufficient protection against SQL injection? (haha it rhymes) I'm not sure what other operations mysql_real_escape_string might perform.
[edited by: SarK0Y at 10:49 pm (utc) on Nov. 28, 2008]
If you don't worry about this (perhaps you know the database setup well) then use this:
mysql_escape_string()This differs from 'mysql_real_escape_string()' in that it does not use a database connection. That is what you wanted - and so you now have the perfect, if depreciated, solution.
(It interests me what you are working on that means mysql_real_escape_string() is a significant performance hit - I've never seen it make any measurable difference whatsoever)...
