Dos and don'ts / Best practices 1

[Sleidia]

From time to time I come across scary statements like this :

2) Your code is using string concatenation to build the SQL used to access the database. You're vulnerable to a SQL Injection attack, and people are taking advantage of the hole in your code (extremely bad). To fix it: change your code to use best-practices database access for your language.

I did use concatenation fefore but now I do like this:

$sql_query[1] = "
SELECT 
*
FROM 
my_table
WHERE 
blahblah
AND 
blahblah
";

$sql_result[1] = mysql_query($sql_query[1], $GLOBALS["_db_connect"]);
$sql_num[1] = mysql_num_rows($sql_result[1]);

Anyway, I'm sure that there are plenty of wrong things that I do and I want to find out about it.

So, in order to avoid sql injections and other security/structural problems, what are the dos and don'ts, what are the best practices?

Depending on the amount of information gathered, this thread could be turned into a FAQ in the future.

Thanks !

 

[thedaver]

Rule #1: Never, ever, never, never directly apply a value taken as input from the browser directly into a SQL query!"

Corollary #1: Never trust browser data without cleaning it up to make sure you're getting the kind of data you expect. It can ALL be spoofed and manipulated.

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[Sleidia]

Hi guys,

That would be great if you could illustrate your rules with practical examples ( a few lines of code ).

Many thanks

 

[Sleidia]

Rule #1: Never, ever, never, never directly apply a value taken as input from the browser directly into a SQL query!"

Corollary #1: Never trust browser data without cleaning it up to make sure you're getting the kind of data you expect. It can ALL be spoofed and manipulated.

Is there a unique cleaning-up routine that could be used for any sort of data? I don't even know what to look for in the submitted string. Limiting the string size isn't a viable option with textareas, right?

 

[webdev007]

Posting many security functions() could mean a lot of typing
read about mysql_real_escape_sring

also I do not know if I may here post a book title
but please read "essential PHP SECURITY"

How do you post a URL on the forum?

 

[AdaHacker]

Sleidia,

The general rule is to use a white-list approach. Validate every parameter for data type, size, format, and anything else that's applicable. And make sure to do ALL of this validation server-side. JavaScript can be turned off and HTML files can be edited and saved locally - only the server-side code is truly under your control.

With string concatenation for queries, the main worry is string parameters. For those, you need to account for quotes and escape characters in the string. Consider, for example, the line:

$query = "select * from users where uid='$uid';";

If $uid contains a quote or an escape character, the user can break out of the quotes around '$uid' and insert another condition into the query, such as by passing the string "'or true; --". Use one of the *_escape_string() functions, or something similar, to prevent this.

Incidentally, this is why string concatenation for queries is such a bad idea. Ideally, we would all be using a database abstraction or query-building framework that handles string escaping and parameter validation for us. However, PHP doesn't come with one of those. I believe there are a few such third-party frameworks out there, but I'm not too familiar with any of them.

 

[AdaHacker]

How do you post a URL on the forum?

Use the link tag. Click that "Process TGML" link for the markup syntax.

 

[jpadie]

However, PHP doesn't come with one of those

c'mon ... it kind of does. check out the PEAR repository. there is PEAR:B which is really easy to use but no longer supported. it has been replaced by PEAR::MDB2 (slightly steeper learning curve).

i agree with Adahacker's white list approach. you can also test by checking type

$bool = $_POST['booleanArg']; //should be true or false;
if ($bool !== (bool) $bool) {//bad data}

similar approaches can be used for each different variable type.

for permitted values you can use checking arrays

$field1Permitted = array("apples", "oranges", "bananas");
if (!in_array($_POST['field1'], $field1Permitted, true)){//bad data}

etc

 

[webdev007]

Well, since you opened the fawcet
Cheking altered values from a DD box

$rating= htmlentities($_POST['rating']); 
$clean_category = array();
switch ($rating)
{
	case '0.0':
	case '1.0':
	case '1.5':
	case '2.0':
	case '2.5':
	case '3.0':
	case '3.5':
	case '4.0':
	case '4.5':
	case '5.0':
	$clean_category['rating'] =  $rating;
	break;
}
if ($rating!=$clean_category['rating'])
{
echo" <h1>Alert! etc....</h1><br>";
	Exit(); 
}

if ($rating =$clean_category['rating'])
{

$_SESSION['rating']=$rating;
$rating=$_SESSION['rating'];
}

 

[Sleidia]

Hmmmm ok but let's take a practical example : the textarea I'm using right now to post this. I'm supposed to be able to send almost any text string to the database. So, how do they do?

 

[webdev007]

Good example:
I use an eregi() to disallow most dangerous terms (drop etc..)
but I do not allow for any brakets and pipes
so you are correct "how do they do it?"
I wouldn't allow most of my script to be posted by any user.
When posted we cannot edit it, so is it somehow locked?
possibly coded but why?
They surely protect injection url based

but to really answer your question: I have no clue!

 

[Sleidia]

but to really answer your question: I have no clue!

You deserve a star for that! ahahaha