insert data safely into MySQL database. (PHP)

[jasonc]

MySQL Injection is something I have been told to think about.

Please can someone give me something more than what I have seen on the net, not got a clue where to look!!

I know that inserting data without checking it first is a no no.

But how, I am wanting to check that the email address IS an email address and that the persons Name is leter from a-z or A-Z and thats it really, all the other info that will go in the DB will be from pull down menu lists so that should be safe to go straight in?

Do I just do a search of the characters entered in each field that they are from A-Z or a-z and 1-9 ?
or is there still more I have to think about?

thanks in advance for your help.

[Caesar]

Another thing to consider, is to make sure the script with the connection to MySQL is local.

Something like:

[code]<?php

if ($_SERVER['SERVER_NAME'] == "www.yourdomain.com")

{// DB Connection Here//}

else

{echo'No Worky.';exit;}

?>[/code]

Of course that is not a solution...as someone could be executing scripts from within your domain. But it is another base to cover

[jasonc]

'local' ?

that a new one on me !!

never even thought of that one. CHEERS.


execute scripts from within my domain ? how, thought i could only do that, or do you mean that if someone tries lets say,
www.site.com/scripts/addentrytodatabase.php
to run a script on its own?

if so, i have thought of that, i have created many scripts all in a different file, all with ambiguos names !!

or is there another way it can be done ?


also how do i actually check that the text they typed in is letter and numbers only?
is there a easy way to check?


thanks

[448191]

SQL injection can be prevented by using common sense.

Like you already said: always distrust any external data. Validate everything.

Example:
mysql_query('SELECT username FROM users ORDER BY username '.$_GET['ORDER']);

This is very, very wrong. You just [u]assume[/u] there is now malicious data in there.

Exploit:
[b]script.php?order=desc; DROP TABLE users [/b]

Yes, that [b]will[/b] drop the table 'users'...

Always validate ALL external data!
[code]if($_GET['ORDER'] !== 'desc' || $_GET['ORDER'] !== 'asc') {
   trigger_error('SQL injection attempt!',E_USER_ERROR);
}
   else {
   mysql_query('SELECT username FROM users ORDER BY username '.$_GET['ORDER']);
}[/code]

Another important note:

When accessing / comparing values, let mysql know you're expecting VALUES!
Example:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]mysql_query('SELECT level FROM users WHERE username = '.$_POST['username'].' AND psw = '.$_POST['psw']); [/quote]

Argh! I can just attach something to the query!
I just create a simple script, setting the targeted script as action. I then send the username I want and $_POST['psw'], containing "anything OR 1=1".

[b]I can login as anyone I like now!*[/b]

The above exploit could be prevented by using quotes (you're expecting a value, not a keyword):
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]mysql_query('SELECT level FROM users WHERE username = "'.$_POST['username'].'" AND psw = "'.$_POST['psw']).'"'; [/quote]

[i]SELECT `level` FROM users WHERE username = "448191" AND psw = "anything OR 1=1";[/i]
Won't get you logged in!

Also, validate the referrer ($_SERVER['http_referrer']), that'll make it a lot harder to sneak in any $_POST variables...

[b]*EDIT:[/b]
Come to think of it: SELECT level FROM users WHERE username = 448191 AND psw = anything OR 1=1 won't get you logged in either: it'll just return all the values of column 'level' in the table!

But you get the idea... [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /]

[redarrow]

valadate email with eregi ok.

if(!eregi("^[a-z0-9_]+@[a-z0-9\_]+\.[a-z0-9\-\_]+$",$email)) {

echo " sorry only valid email address allowed";
}



448191
can you kindly x the EXploit: code out please cheers.

[448191]

[!--quoteo(post=372867:date=May 10 2006, 05:29 AM:name=redarrow)--][div class=\'quotetop\']QUOTE(redarrow @ May 10 2006, 05:29 AM) [snapback]372867[/snapback][/div][div class=\'quotemain\'][!--quotec--]
448191
can you kindly x the EXploit: code out please cheers.
[/quote]

That was a typo and I was still editing, chill out. Running for mod or what?

[redarrow]

[!--quoteo(post=372874:date=May 10 2006, 10:46 AM:name=448191)--][div class=\'quotetop\']QUOTE(448191 @ May 10 2006, 10:46 AM) [snapback]372874[/snapback][/div][div class=\'quotemain\'][!--quotec--]
That was a typo and I was still editing, chill out. Running for mod or what?
[/quote]

sorry for asking

[448191]

That's okay, just give people a break. I feel I do a lot to make my posts readable, like using caps when required... [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]