CYA against MySQL Injection attacks

[southbeach]

Hello!

I just came across this construct using a WHERE clause:

	$query.= "WHERE file_data.id = '".$data."' ";
	$query.= "OR  file_data.booking_num = '".$data."' ";
	$query.= "OR  fie_data.po_num = '".$data."%' ";
	$query.= "OR  file_data.container_num = '".$data."' ";
	$query.= "OR  file_data.shipping_num = '".$data."' ";

First thing that jumps to mind is that I need to insert mysql_escape_string() and wrap $data with it.

Now, to explain the potential security hole with above snippet, what would be a sample string user could type on processed form?

I am afraid that this may be a wide range problem across the application and I am looking for solid and must be able to proof so that I am not thought of as one trying to take advantage ...

Thank,



--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[DonQuichote]

' OR (1=1) --

is enough.

As another precaution, NEVER send database IDs to a browser. Really. They belong in a database and should never leave your web server. So hash them, put a lookup array is the session and send those hashes to the client only. That way, an unknown hash really is unknown.

And what if you don't? Say you have a banking web site. You allow to transfer from one of the client's accounts to another. If you can just submit another database ID for the account ID, an attacker can just guess one and have the amount written off somebody else's account.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[southbeach]

DonQuichote,

I have tried your string and others and it is not breaking the page. Looking further, I found that the search form is submitted via XHTMLREQUEST OR AJAX using GET method.

$data is set simply by using a trim($_request['var_name']).

I am going to look at the JS to see if the value is escaped prior to passing it to the search script.

Any thoughts?

Thanks,

--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[jpadie]

the data may well be urlencoded by the js. but this does not equate to escaping. Different databases use different escaping schemas. the most popular being simply to double the single quotes. by the by i now use PDO prepared statements to handle enquoting an escaping.

DonQuichote's example will not 'break your page' but it will work as a sql injection attack and thus will return more results than would otherwise be returned (it should return the complete result set).

Unless, of course, your script is doing some proper input management before running the query. i.e. the script is expecting an integer and so will intval() the incoming value before using it in the query. If you use intval you need not use either enquoting or escaping.

Consider using html_quickForm for data submission purposes. in a recent project which was heavily data driven, it has saved me days of programming. Many people shy away from the class because it limits your ability to change the display of forms. Firstly this problem is much reduced through the use of table-less forms + css, and secondly there is no requirement to use quickform to display the form. you can simply use it as a validator and processor if you want and display your own html coded form.

 

[feherke]

Hi

I have tried your string and others and it is not breaking the page.

Probably you have [tt]magic_quotes_gpc[/tt] turned on in your php.ini. It is deprecated and you will get a warning in PHP 5.3.

will return more results than would otherwise be returned

Be abit more paranoid. It can also [tt]delete[/tt] or [tt]update[/tt] more records than would otherwise be affected.

Feherke.

http://rootshell.be/~feherke/

 

[jpadie]

true enough.

i'm also confused by the wildcard in the po_number line. i wonder whether this should be a LIKE operator instead of = ?

 

[jpadie]

NEVER send database IDs to a browser

i've been pondering this for a while. to come clean i do not follow this mantra although i see the sense. for internal apps i just don't bother with any occlusion. for things which other people may use i enlist the help of nonces. for everything that is not idempotent i set a nonce that relates to the object (i.e. database id) and action that is permissible. the nonce is embedded into a form or url and is checked on submission. if the nonce does not exist or is invalid the action is not taken.

i guess this has the same net effect as DQ's advice.

 

[feherke]

Hi

NEVER send database IDs to a browser

Well, for example in case the session is stored in a database and the session table's id is stored in the login cookie, is indeed the worst idea. True story of one, mostly intranet web application :
[ul]
[li]You, poor common employee, siting at your machine.[/li]
[li]Wait until the boss, the one with more permissions then you, logs in into the intranet web application.[/li]
[li]You log in too.[/li]
[li]Take a look at the session id stored in your login cookie.[/li]
[li]Knowing that your boss logged in shortly earlier, decrement the session id in your login cookie and reload the page.[/li]
[li]Repeat the previous step until the reloaded page greets you as boss.[/li]
[/ul]
I would say, DonQuichote's advice is valid for cases when by id editing one could reach data otherwise not accessible for him. But in case of a product id, for example, I see no reason for encryption. Not sure this file_data.id which case is.

Feherke.

http://rootshell.be/~feherke/

 

[southbeach]

Hello guys/gals!!!!

Wow, this is the very reason why I love this group - Thank you all !!!!!

That being said, I confirmed that magic_quotes_gpc is ON and I am already addressing it.

This is an application I just inherited and am going through it trying to find flaws as I am learning how it was written; kind of getting on the original programmer's head.

I googled SQL Injection and read through a few pages. I find this to be a fascinating subject and an alarming one at the same time.

I will dig deeper into this and make sure our application is solid.

Thank you all once again for you invaluable advise and input !!!!


--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.