How to stop SQL Injection

Hi,

I was just wondering what everyone does to prevent SQL injections and what are the best ways to secure your code.

As i am wanting to learn how everyone goes about doing this and learn the best methods to protect my code.

Cheers,
Adam

 

For numeric/integer values:

$foo = intval($foo);

PHP:

www.php.net/intval

And for any other data:

$foo = mysql_real_escape_string($foo);

PHP:

www.php.net/mysql_real_escape_string

Do never ever trust the user or think "Nah, they won't do or try this". You can also use preg_* functions to filter the input for what it needs to be.
For example if you only want to allow characters from a-z you can do:

$foo = preg_replace('/[^a-z]/i', null, $foo);

PHP:

www.php.net/preg_replace

Or if you have a select for example, and you want to make sure the submitted value exists in the select, do something like:

$allowed_values = array('foo', 'bar', 'etc');

if (!in_array($_POST['select'], $allowed_values))
{
    // Handle error
}

PHP:

I repeat, don't trust anyone. Think about every variable in your query string and make sure the user has no chance to insert something he isn't supposed to.

 

A good habit is to wrap all your SQL variables inside your own function without any quotes.

$sql = "SELECT * FROM table WHERE something=".safe($_POST['value']);

Code (markup):

then your safe(...) function would take care of all the quotes, cleanup, warnings, (or whatever), needs to be done.

Look at the quote_smart(...) function on php.net, http://www.php.net/mysql_real_escape_string

But you will need to improve it a little to be safer, (read some comments).

And be sure to check for invalid requests from the users, ($_GET/$_POST/$_COOKIES etc). You should have one common script to always validate your requests.
As mentioned, never, ever trust anything/user, (especially the registered users).

Even inside your own internal functions that you think will never be used with outside variables, always enclose your SQL code inside a safe function.

And if you are paranoid, (like me), use your own function mysql_query(...) and get it to email you when there is an error, (because there should not be any errors anyway).
Most hackers will do a trial an error before fully injecting your code, so those initial errors would be reported by your safe function and hopefully allow you to react before it is too late.

Did I mention, never trust anybody! Open Source, reputable, code does not always mean safe.

FFMG

 

Here the small article:

All anymore and more coder start making in the scripts co-operating with the bases of information. As a rule, MYSQL is utillized (rarer MSSQL). In this article I will tell about the measures of defence of Php+mysql, as with MSSQL I do not work and it gives where more possibilities are for a hacker, what MYSQL. Essence of uyazvimostey of such type consists in that through variables which are passed through a parameter to the script and participate in a SQL, a malefactor can inculcate the SQL-code and the same to modify a query to DB. I not will in detail sharpen attention on principle of SQL-inj, but will simply tell how to avoid them. Those, who wants to know, as there are SQL-inj, can read about them here. I will make a not large example:

SELECT * FROM table WHERE user='$go'

Assume this variable $go passed as a parameter to the script and Sql-comands is executed. All would good, that a hacker can as a value to to to pass user=lamer' (you will pay a regard to quotation mark) a change and to close a condition, and further to inculcate through f-ciyu UNION (this f-ciya appeared with Mysql4, and in more early versions of inj will not turn out) other command SELECT and the same to get confidential information from a base (for example, passwords) if knows its structure. If after a condition would go yet what both other terms (not necessary a hacker) or he would be able easily to cut them so "-" or so "/*" (these characters in MYSQL mean a further comment). By the way, in MSSQL of malefactors would put character ";" (meaning end of command) and to inculcate new (for example, INSERT, UPDATE etc.).

For the decision of problem with SQL-inj we must filter transferrable in Sql-kommande variables. It is arrived at the followings methods:
1) Never forget to put a value in quotation marks (it will be useful for us for further filtration of variables). I.e. so user='$go', but not so user=$go ! By the way, if a variable is a number, its value also can be specified in quotation marks.
2) If a variable is a number, securing it is possible as easy as anything, it is necessary simply facilities of PHP to bring it over to the numerical type:
$go=(int)$go; or $go=intval($go);
3) If a variable is not a number, we need simply to watch absence of quotation marks, it can be obtained f-ciey mysql_escape_string() (its analogue is addslashes()). F-ciya is intended in an order to put slesh before a quotation mark (\'). Now even if a hacker will make an attempt inculcate the SQL-код, he will not exceed a supply quotation marks.

If you are paranoikom, can do filtration on basic f-cii in MYSQL, which are utillized for injections: UNION and CHAR.

Code (markup):

 

nico_swd's post is very good summary, your code should be safe if you follow that.

 

FWIW, make sure to turn off PHP's magic quotes before you escape your strings or you could be double-escaping. But do turn if off, it sucks.

 

Cheers guys... good to hear everyones input and give me some good ideas to include.

I like the idea to create one big function to apply to all my $_POST

What would you reckon would be the ultimate function to use to make sure everything is checked?

 

It depends, what do you mean by checked?
all the function would do is ensure that the string are properly formated, (the quotes are escaped).

But surely you don't only use the $_POST/$_GET for SQL?

FFMG

 

I mean a function to strip tags, stripslashes, trim, check if numeric or not add intval or mysql_real_escape_string depending on what the string is. Basically do everything needed.

 

this is great advice but how do you use within a vBulletin
hOW do you use this sir - nico_swd
/showthread.php?p=11718096#post11718096

 

sorry for the double post .... i will try to delete



This is what I was looking for .... but how do you use it ????


In vBullitin how is it applied?

Example for instruction :

In Admincp
Styles and templates
Search and Replace what????


........ please help a guide?

 

Escaping strings does add some protection, but even if strings are escaped you can still be vulnerable...using parameterized queries is important. Good explanation here that's more in depth than I can go into on a forum:

http://www.programmerinterview.com/index.php/database-sql/sql-injection-prevention/

 

This is not true, escaping strings will work fine.
Not escaping strings properly, that's the problem.

Any example you might have will only highlight badly escaped strings.

FFMG

 

Uhm... wow, people STILL advocating bad techniques and broken methodologies -- completely ignoring the GIANT RED WARNING BOX ON EVERY MYSQL_ FUNCTION PAGE saying... don't use mysql_ anymore.

... and really that's the PROPER answer to this question -- do NOT use mysql_ that way you aren't blindly adding strings together which is how you end up shtupped in the first place! I mean I know people are still supporting old/outdated code... but really is it THAT hard to change out the broken assed insecure idiocy for a nice clean safe object implementation?

I haven't done an escape manually in half a decade, because I did what they said to do -- use prepared queries using PDO or mysqli.

// assumes $db is a PDO object that is connected to the database
$statement=$db->prepare('
	SELECT * FROM table
	WHERE something = :something
');
$statement->execute(array(
	':something' => $_POST['value']
));
foreach ($row = $statement->fetch()) {
	// process it here
}

Code (markup):

100% injection-proof... it's not rocket science. Leave the garbage mysql_ functions and nonsense like manually sanitizing values where it belongs -- in the previous decade. JUST AS PHP.NET HAS BEEN TELLING YOU SINCE PHP 5 DROPPED.

Herpafreakingderp people...

-- edit --

Ok, maybe a little less froth on that, didn't notice this was a bump of a six year old thread. Of course with the effectively invisible light grey on white text for dates, placement of dates in the thread where I wouldn't actually be looking for dates, this is hardly a surprise... something ELSE to override with user.css I guess.

 

Yes, I made the same mistake, I also replied to a 6 year old thread...

FFMG

 

hello everyone..how do i code PDO for this script...please help me


<?php



if (isset($_POST['Login'])){

$UserName=$_POST['UserName'];
$Password=$_POST['Password'];

$hostname = 'localhost';
$username = 'root';
$password = '';
$database_name = 'ovs';
$mysqli = new mysqli($hostname, $username,$password, $database_name);

$login_query=mysqli_query($mysqli,"select * from voters where Username='$UserName' and Password='$Password' and Status='Unvoted' and Year='1st year'") or die(mysqli_error());

$login_query1=mysqli_query($mysqli,"select * from voters where Username='$UserName' and Password='$Password' and Status='Voted'");


$count=mysqli_num_rows($login_query);
$count1=mysqli_num_rows($login_query1);


$row=mysqli_fetch_assoc($login_query);
$id=$row['VoterID'];
?>

 

You should start your own thread rather than replying to a 7 years old thread.

FFMG

 

ok i will thanks