Sql Injection Prevention (passing Numerical Data Across Pages). PHP/mySQL

[Chesso 0]

Even if your building something as simple as a basic news page for your website, if your passing along url variable strings like (mysite/index.php?page=1), you may be vulnerable to SQL injection attacks.

 

For cases like these (passing numerical data in url strings), I have a handy dandy little function to thwart these attempts silly:

 

// For checking if value is a number, if not return 1. function isNum($val) {  if (!is_numeric($val)) { $val = 1; }  return ($val); }

I have this function, within my functions.php file, which I use as an include in files where I need access to the function, and use it like so:

 

</php.....include 'functions.php';....$page = isNum($_REQUEST['page']);?>

So if someone decided to pass along (mysite/index.php?page=1P, or anything non numeric at all), it will be reset to 1. This will halt anything other than the intended numeric data (or a static base numeric value) getting in.

 

Of course 1, might not be the desired alternative numeric value, so you could modify the function to be something like this:

 

// For checking if value is a number, if not return 1. function isNum($val, $alt) {  if (!is_numeric($val)) { $val = $alt; }  return ($val); }

Which would basically allow you to specify an alternative numeric value, so if the url sent one isn't, it will use our alternative, here's an example of it's use:

 

</php.....include 'functions.php';....$page = isNum($_REQUEST['page'], 1);?>

So if $_REQUEST['page'] is anything other than valid numeric data, in this case, it will become 1.

 

I hope this information is of use to you all

 

P.S. Feel free to comment/suggest etc, also if you know any other little things like this to help out against SQL Injection (or even XSS etc), I would be more than happy to read them, I am very interested in the subject of preventing these kinds of things (especially without going overboard).

[miCRoSCoPiC^eaRthLinG 0]

Good tip... anyone who's into designing a blog/CMS/Forum software or just a plain web-site which uses a navigation method based on URL encoded variables should implement such a check from ground up. If this trick is kept on mind and integrated into the core of the system, it can save many tears later on

[TavoxPeru 0]

Another way to prevent Sql Injection attacks is by using the mysql_real_escape_string() php function if you use the mysql php extension or the mysqli_real_escape_string() php function if you use the mysqli php extension, both functions do the same thing, escapes special characters in a string for use in a SQL statement and are very helpful, i use it always, and as you i code a little function and included it in every page that works with databases.

 

Visit MySQL - SQL Injection Prevention to see a good explanation with examples of this issue.

 

Best regards,

[Chesso 0]

Yup I do the same for string data that's parsed.Numerics I use this function, it's smaller, faster and if it ain't a pure numeric value (I don't want it) .On all string data in my custom function I perform strip_tags, str_replace (if I don't want \r\n for single line strings etc), mysql_real_escape_string() etc.One thing I haven't determined yet is how to avoid indirect injections (like form data). It's one thing that came up using a vulnerability scanner even after using the above techniques, though I suspect it's less likely to be taken advantage of.

[TavoxPeru 0]

Another article that i find relating Sql Injection attacks can be read at SQL Injection Attacks: Are You Safe?, this one is a bit older but may be can help.

Best regards,

[TavoxPeru 0]

More articles with a lot of examples:

SQL Injection Attacks by Example,Steve Friedl's Unixwiz.net Tech Tips.

(more) Advanced SQL Injection,Chris Anley, Next Generation Security Software.

SQL Injection walkthrough, SecuriTeam.

PHP: SQL Injection - Manual, PHP Official Documentation. (New)

All of them are very complete.

 

Best regards,

Edited June 10, 2007 by TavoxPeru (see edit history)

[sparkx 0]

Very nice Tutorial. I just need a little more help. I wan't to stop sql injections from form inputs (post inputs) is there a way so I can make it so only the charictors a-z A-Z and 0-9 can be entered? I tried preg_match but I found out if you have just one letter in it then return will work (example: - doesn't work but a- does). How would I make it so a- would not return and come up with an error message. That would really help make my website secure. I really don't want my site to get hacked lol.Thanks,Sparkx

[TavoxPeru 0]

Very nice Tutorial. I just need a little more help. I wan't to stop sql injections from form inputs (post inputs) is there a way so I can make it so only the charictors a-z A-Z and 0-9 can be entered? I tried preg_match but I found out if you have just one letter in it then return will work (example: - doesn't work but a- does). How would I make it so a- would not return and come up with an error message. That would really help make my website secure. I really don't want my site to get hacked lol.Thanks,
Sparkx

Well you have a lot of options to do that, one way is to check on the client side all your form inputs with a javascript function, if the data is correct you submit your form otherwise you show your error message. For example:

<html><head><script type="text/javascript">function isAlphaNumeric(str){  var re = /[^a-zA-Z0-9]/g  if (re.test(str)) return false;  return true;}function checkForm(TheForm){	var nf = TheForm.elements.length-1;	var f = TheForm;	for(i=0; i < nf; i++) {		e = f.elements[i]; // element		v = e.value; // element value		if (v != "" && isAlphaNumeric(v) ) continue;		else { e.focus(); alert('Error'); return false; }	}	return true;}function Check(elem) {	var v = elem.value;	if ( v!= "" && isAlphaNumeric(v) ) { alert("Correct value"); return true; }	else { alert("Incorrect value"); elem.focus();return false; }}</script></head><body><form action="page.php" name="a" onsubmit="return checkForm(this)" method="post"><p>Text to validate with onsubmit: <input type="text" name="aText" value="" size="10" maxlenght="5" /><br />Text to validate with onblur: <input type="text" name="aText1" value="" size="10" maxlenght="5" onblur="Check(this)"/><br /><input type="submit" name="submit" value="Submit" /></p></form></body></html>

Take in mind that this is a very simple working example so you must adjust it basically to show the error messages.

Best regards,

[Chesso 0]

Keep in mind javascript (being client side), can be modified by the user if they know how.A more secure method is to check server side (validation through a PHP script or some such), just make sure you strip out anything dangerous before validating any input.

[sparkx 0]

Well I tried this. I know it doesn't work all the time but is it good enough in most cases? Code:

//STOPING ALL POSSIBLITIES$var=$_POST['var'];$no_good = array("'", '"', ">", "<", ";"); //Possible charictors used in injections$var2 = str_replace($no_good, "", $var);if($var!=$var2){echo("Invalid Charictors Used.");exit();}//CONVERT TO HTML$var=$_POST['var'];$no_good = array("'", '"', ">", "<"); //Possible charictors used in injections$no_good2 = array(""", '"', ">", "<");$var2 = str_replace($no_good, $no_good2, $var);

Which one do you recomend for safe results. If I do convert to html can they do html on my site or would it just be displayed and no action taken?
Thanks,
Sparkx