Welcome to WebmasterWorld Guest from 54.144.68.27

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

SQL Inject

     

SarK0Y

10:34 pm on Nov 23, 2008 (gmt 0)

5+ Year Member



Good Time for All, Amigos!
i want to discuss my choose for fight with it.
so, idea: to use regexp for catching keywords: select, insert, delete, update, ';', AND, OR. but i think, may to use only triple: ';', AND, OR. whether am i right?

vincevincevince

1:39 am on Nov 24, 2008 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



This is not a good way to fight it. Delimiting characters are the key to fighting SQL Injection. Make sure that the text which comes from your user does not contain anything which might be interpreted as delimiting.

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;

Gives: 1\' OR 1 OR \'1

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).

SarK0Y

3:07 am on Nov 24, 2008 (gmt 0)

5+ Year Member



Hi, vincevincevince.
thanks for your reply, but say me, please, mysql_real_escape_string is absolute guard against attacks with same type? moreover, i want will make it for clear experiment. secondly, if is func for fight it, we can write code without looking back for it - we simply apply AntiInject function;-)

vincevincevince

3:19 am on Nov 24, 2008 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



mysql_real_escape_string is absolute guard against attacks with same type

It is the best guard there is, so long as you use it correctly.

SarK0Y

4:23 pm on Nov 24, 2008 (gmt 0)

5+ Year Member



Hi, vincevincevince.
ok, Amigo, ask me, please, what is better: send dirty request to db or throw away it after parsing by script. and, let's to mark one thing, my user have desire to write sign ('DELETE FROM `users`'). and so, i must prevent his desire?:)

PHP_Chimp

6:17 pm on Nov 24, 2008 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



send dirty request to db

Dont do it, ever. So you use something like mysql_real_escape_string as a final attempt to remove anything that is left after your anti-attack function.

or throw away it after parsing by script

No point in throwing it away. Send it back to the person and ask them to complete it properly. So if it is a string then you could rule out certain characters.
So if it is supposed to be a name then you may only allow a-z (depending on if you are going to allow accented characters or far eastern characters).
A number filed could be tested with intval or a regex.
So there is no problem with using a regex to test the values, just dont just rely on that alone, use the mysql_real_escape_string as well.

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.

SarK0Y

8:34 pm on Nov 24, 2008 (gmt 0)

5+ Year Member



so, Amigos, what do you say about funny way:
$PMtext=urlencode($_GET["PMtext"]);?

SarK0Y

8:18 pm on Nov 25, 2008 (gmt 0)

5+ Year Member



i solved to make so:
> for 'LONGTEXT', compress string and include it to db as 'VARBINARY'.
> for 'VARCHAR' and 'DATE', restrict range of the symbols via regex.
-----------
of course, compressing has bad side - we can't use 'LIKE'. but we have space economy.

vincevincevince

3:17 am on Nov 26, 2008 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



For space economy, use a compressed file system.

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.

SarK0Y

7:44 pm on Nov 26, 2008 (gmt 0)

5+ Year Member



Hi, vincevincevince.
>>> For space economy, use a compressed file system.
it's non-good way: speed of the db will be low very much.
>>>Run the str_replace... end up with: DELETE FROM `users`
bad example: 'DELETE' must be replaced to '1DELETE' but and it's not better approach.
i see two methods: first method (my post 8:18 pm on Nov 25, 2008) and second road replaces symbols [']/[quote] via fit escape codes. if it's necessary, we can replace and other special symbols. given way makes a balance between string's length inputed to db and having a possible to use 'LIKE'.

vincevincevince

1:44 am on Nov 27, 2008 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



>>> For space economy, use a compressed file system.
it's non-good way: speed of the db will be low very much.

Only during database startup - once the database is running it should all be in memory, where you want things uncompressed for fast access and deployment. Compressed file systems only impact databases when the server has insufficient RAM.

SarK0Y

2:36 am on Nov 27, 2008 (gmt 0)

5+ Year Member



Hi, vincevincevince
i dont argue with it, but data saving needs call hdd and speed with same fs will be low; compressed fs needs more ram and cpu time. in consideration of all it, we get system with less safe.

PHP_Chimp

8:32 pm on Nov 27, 2008 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



Why does a system that uses more ram and cpu time mean it is less safe?

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.

SarK0Y

11:34 pm on Nov 27, 2008 (gmt 0)

5+ Year Member



Hi, PHP_Chimp.
>>>So why do you not want to use mysql_real_escape_string?
it doesn't solve some cases: i want insert string to db in any case.
>>>Why does a system that uses more ram and cpu time mean it is less safe?
the operation takes less time the probability a fail less. this rule is absolutely right for db and other tasks.

SarK0Y

2:21 pm on Nov 28, 2008 (gmt 0)

5+ Year Member


we can use so way:
function safeStr(&$str, $link)
{
$escapeCode=Array("!1", "!2");
$q=Array("'", '"');
$str=str_replace($q[0], $escapeCode[0], $str);
$str=str_replace($q[1], $escapeCode[1], $str);
mysql_real_escape_string($str, $link);
echo $str."\n";
}
mysql_real_escape_string escapes [quote] to \", it may make safe but wrong db query. we hide [quote] to "!2" and ['] to "!1", then, for lazy people:), we apply mysql_real_escape_string to escape other special symbols. though, i must mark one thing, this func reduces query speed and to optimize it we should neglect this func and assign necessary symbols for escape without it.

[1][[b]edited by[/b]: SarK0Y at 2:28 pm (utc) on Nov. 28, 2008][/1]

npwsol

8:05 pm on Nov 28, 2008 (gmt 0)

5+ Year Member



You are still thinking of it wrong, SarK0Y. mysql_real_escape_string will already replace the [ ' ] and [ " ] characters, but they will be replaced with [ \' ] or [ \" ] respectively.

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!

SarK0Y

9:32 pm on Nov 28, 2008 (gmt 0)

5+ Year Member



Hi, npwsol.
yeah, Amigo, you are right - both func work same but my suggestion doesn't need actual db connection, query will be right in any case. and now about your example (I need two of them!1). probability this occurrence isn't high and we can apply other replacements, which are more rare.

npwsol

9:39 pm on Nov 28, 2008 (gmt 0)

5+ Year Member



Ah, I see your reason for not using the function now. I cannot imagine the case where you are not already connected to the database, but I do not know your code. :)

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.

SarK0Y

10:48 pm on Nov 28, 2008 (gmt 0)

5+ Year Member



npwsol
>>>> I cannot imagine the case where you are not already connected to the database.
firstly, parse strings and anything other operations, then open connection and insert data to db.
>>>You could also use addslashes, which would escape
ok,Amigo, it's good way and may use addcslashes or quotemeta too. all outrage put into db without injection:)

[edited by: SarK0Y at 10:49 pm (utc) on Nov. 28, 2008]

SarK0Y

12:57 am on Nov 29, 2008 (gmt 0)

5+ Year Member



for justice, next fact must be marked: mysql_real_escape_string works subject to codepage of the db. for my current project doesn't need it, in any event now. but forum's engine will be need this feature exactly.
----------------------------------------------
My sincere gratitude to All after our criticism/comments.

vincevincevince

6:36 am on Nov 29, 2008 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



You will need to respect the codepage/encoding of the database - because when you replace ' with !1 you will not replace ' when it is a multibyte ' and if the database is multibyte then this will allow the ' through and make things insecure.

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)...

SarK0Y

6:12 pm on Nov 29, 2008 (gmt 0)

5+ Year Member



vincevincevince
>>>It interests me what you are working.....
:) i'll say so: it isn't more than my caprice/experiment, now:)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month