SQL sanitise input

[Craig Hopson]

hi guys how can i stop SQL injections in this script

$getcat = mysql_query('select * from '.tb().'downloads_cat where id = '.$_GET['id'].'');

[birbal]

Don't trust user inputs $_GET,$_POST,$_COOKIE. filter/sanitinize user inputs. use mysqli_real_escape_String() or more better prepared statements.More info:http://in3.php.net/m...ty.database.phphttp://in3.php.net/m...l-injection.phphttp://in3.php.net/m...y.variables.php

Edited August 17, 2012 by birbal

[Craig Hopson]

Like this?

$getcat = mysql_query('select * from '.tb().'downloads_cat where id = '.mysqli_real_escape_String($_GET['id']).'');

[boen_robot]

Switch all MySQL functions to the MySQLi equivalents. I'd also advise you to use the object API, although using functions is OK too.Look at mysqli_real_escape_string()'s manual page for examples.

[Craig Hopson]

What is object API and why mysql-I

[boen_robot]

I mean "object oriented style". Look at the linked page. At the top of each example, you'll see "object oriented style" and "procedural style".Basically, "object oriented style" is that thing with "new" and "->" in it.

[birbal]

why mysql-I

mysql api is obsolette it is for previous mysql engine. mysqli(i for improved) for newer engines

[Craig Hopson]

Oh I see thanks guys I'll go read one thing are all the old mysql functions duplicated to mysqli

[boen_robot]

They all have equivalents, yes, though they're not all "duplicated", i.e. it's not always as simple as replacing "mysql_" with "mysqli_".

[Krewe]

I'm gonna join this topic, because I've heard more about mysqli and I figure I need to switch ASAP.My questions:1) Where can we find a list or something where we can find the appropriate mysqli_ command for the current mysql_ we are using?2) I still need to learn OOP PHP, so is there any difference using mysqli_ with non OOP PHP? Thanks GuysKrewe

[Don E]

To answer question 2... no there is no difference really because basically everything that you can do with OOP mysqli, you can do with the procedural style mysqli. The PHP developers made it where mysqli can be implemented procedural or OO.

[boen_robot]

... and to answer 1, the PHP manual itself. If you compare it the MySQL functions with their MySQLi equivalents, you'll see the stuff that exists in both is also called the same, expect the "mysql_" being replaced with "mysqli_".The only reason that sometimes it's not as simple as doing just that replacement is that some MySQLi functions have changed the order of arguments. A notable example is exactly mysqli_real_escape_string(), which if used in procedural style requires the connection as a first argument. mysql_real_escape_string() accepted the connection as an optional second argument instead.

[Craig Hopson]

ok guys ive tried and tried but cant figure this out here are 2 functions from a class I KNOW its vulnerable but i cant seem to stop them from getting my batabase details

		function index(){		$cat = sql_query('select * from '.tb().'downloads_cat order by category asc');		set_title(t('Downloads'));		section_content('<ul class="gallery">');		while($row = mysql_fetch_array($cat)){		$available = sql_query('select * from '.tb().'downloads where cat_id = "'.$row['id'].'"');		$numav = mysql_num_rows($available);		section_content('<li>		<div valign="top"><a href="index.php?p=downloads/downloadpg&id='.$row['id'].'" ><b>'.$row['category'].'</b></a></div>		<div valign="middle" style="height:30%;"><font size="1">'.$row['description'].'</font>	  		</div>		<div valign="bottom" style="bottom:0px;"><a href="index.php?p=downloads/downloadpg&id='.$row['id'].'" title="View Details"><img src="'.$uhome.'/modules/downloads/Folder.png" /></a><br><font size="1">Available<br />'.$numav.'</font>		</li>');		}		section_content('</ul>');		}	  function downloadpg() {$getcat = mysql_query('select * from '.tb().'downloads_cat where id = '.mysql_real_escape_string($_GET['id']).'');$catname = mysql_fetch_array($getcat);  set_title(t(''.$catname['category'].''));$filedetails = sql_query('select * from '.tb().'downloads where cat_id = "'.mysql_real_escape_string($_GET['id']).'" order by download_total desc');section_content('<div style="font-size:1.5em;">'.$catname['category'].'</div><br><A HREF="downloads" onClick="history.back();return false;">Back</a>');section_content(' <ul class="gallery">');while($row = mysql_fetch_array($filedetails)){  section_content('   <li>   <div valign="top" style="height:18%;"><a href="index.php?p=downloads/viewdetail&id='.$row['download_id'].'" ><u><b>'.$row['download_title'].'</b></u></a></div>  <div valign="middle" style="height:40%; ">'.$row['download_desc'].'  <div valign="bottom">   </div>  </div>   <div valign="bottom" style="height:13%;">  ');     section_content('  <a href="index.php?p=downloads/viewdetail&id='.$row['download_id'].'" title="View Details"><img src="'.$uhome.'/modules/downloads/download-button.png" /></a><br><font size="1">Downloads: '.$row['download_total'].'</font></div>  </li>');  }  section_content('</ul>');}

The full class can be viewed HERE EDIT:-Ok ive put this

$_GET['id'] = preg_replace('/[^0-9]/', '',$_GET['id']);

at the top of the class would this be ok? id will always be a number! Will this stop SQL injections?

Edited August 20, 2012 by Craig Hopson

[birbal]

validation is first and basic security step regardless of sql injection.preg_replace() replace string by regular expression you should use preg_match()http://php.net/preg_match for validation. preg_replace() usually used in filtering. in such simple case like ineteger check better to use ctype_*() http://php.net/ctype_digit. regular expressions are resource coastly and should be avoided where it is possible in simple case untill you need the power of regex. particularly for protecting from sql injection we had post the information and methods and links http://w3schools.invisionzone.com/index.php?showtopic=44735&view=findpost&p=248938 http://w3schools.invisionzone.com/index.php?showtopic=44735&view=findpost&p=248931to resourece.

Edited August 20, 2012 by birbal