prevent sql injections

[sridharao]

There are scores of articles out there on this, the more I read the more I get confused. Can anyone here explain in clear "classroom-like" manner about the basics involved.

All I have been doing to the posted form elements is to add stripslashes() not knowing if it is sufficient.

There are magic_quotes, mysql_real_escape_strings, htmlentities() or htmlspecialchars() etc.etc. I have a confusion on their usage. How and when should these be used? Which is more appropriate and when?

[SambaNeko]

It needn't be retyped, so here's a pretty straight-forward explanation that shows you how to use mysql_real_escape_string(), which is the preferred method in PHP.

Please ask any further specific questions you may have though.

[chunk]

here is an example of performing SQL Injection and stop it. The Source code

http://www.pissupoosa.com/forum/f130/perform-and-stop-sql-injection-with-example-t7039/

[kows]

here is a post I made not too long ago. you should definitely use mysql_real_escape_string() (rather than stripslashes, because stripslashes is not sufficient), or use a different MySQL extension like MySQLi.

[KiwiDexter]

Not sure if this will help but the function I got recommended.

<?php

	function text_process($value){
		$value = trim(strip_tags($value));
		$value = addslashes($value);
    	$value = htmlspecialchars($value);
    	return $value; 
	}

?>

[penagate]

1. Magic quotes is bad. Turn it off. If your code is meant to be portable, you can check for its presence and reverse the effect:

if (@get_magic_quotes_gpc())
{
  array_walk_recursive($_GET, "stripslashes");
  array_walk_recursive($_POST, "stripslashes");
  array_walk_recursive($_COOKIE, "stripslashes");
} 

(Side note: stripslashes reverses the effect of magic quotes; you're thinking of addslashes, which does the opposite.)


2. Use a data access library which supports query parameters: PDO in PHP 5, or MDB2 in PHP 4, or mysqli in either. (The latter is MySQL-specific; the former are not.) Using parameters ensures that there is no chance of the query being manipulated, since the data is properly separated from the query syntax.

Any other solution is weak, and you will spend all of your time chasing the places where you forgot to use addslashes, or mysql_escape_string, or whatever.

[sridharao]

I think my understanding of "sql injection" is poor. I had trouble understanding why should slashes be added or removed. And what is this all about magic quotes. Then I came across this wonderful article http://dev.mysql.com/tech-resources/...curity-ch3.pdf

This answered most of my queries on security related issues.

[SambaNeko]

I think the simplest way I can put it is that SQL Injection turns your intended, normal SQL queries into unexpected, erroneous and/or harmful queries. This occurs either purposefully or unwittingly when users input data with special characters that are reserved for use as part of SQL's syntax. The need to add slashes is to let SQL know that the character is part of the data and not part of the syntax of your query. This is called "escaping" the character(s).

For example, say your user inputs "O'Conner" as their last name into a form. You then have an SQL query set up that looks like this:

$sql = "INSERT INTO customers (lastname) VALUES ('".$_POST["lastname"]."')";

When you put in the $_POST data without escaping the special character (the single quote, in this case), the query is now:

INSERT INTO customers (lastname) VALUES ('O'Conner')

The single quote in "O'Conner" has now become part of the syntax of your query, and you will get an SQL error if you try to run it. To add a slash in front of the quote is to escape the character and let SQL know that it's not syntax, but data. mysql_real_escape_string() is a function then that knows which characters need to be escaped, and will add slashes in the appropriate places for you.

As for magic quotes, this is a PHP setting that, when on, will add escaping slashes to all special characters found in all HTTP request data (POST, GET and COOKIE). The purpose of this was to prevent SQL injections without the programmer even having to know or do anything, because the characters were already escaped when the data went into a query.

However, Magic quotes should not be used and is being phased out of PHP completely for the reasons you can read about here.

[chunk]

i would suggest to use magic quote ON because if the magic quotes are off. hackers can easily write their own files to your writable directory. with the use of

INTO OUTFILE

OR

INTO DUMPFILE

i might be wrong but it could be a reason also

[penagate]

I think my understanding of "sql injection" is poor. I had trouble understanding why should slashes be added or removed. And what is this all about magic quotes. Then I came across this wonderful article http://dev.mysql.com/tech-resources/...curity-ch3.pdf

Thanks for posting that link. That is a good article.

[penagate]

i would suggest to use magic quote ON because if the magic quotes are off. hackers can easily write their own files to your writable directory. with the use of

INTO OUTFILE

OR

INTO DUMPFILE

i might be wrong but it could be a reason also

Wrong.

Magic quotes is a band-aid fix to a problem caused by bad code. It merely disguises the problem, as well as causing unexpected side effects. (Why should all input variables be passed through addslashes? What if you don't want them to be sanitised?)

Read the article linked by Sridharao above. It contains lots of suggestions for improving your data access code. Most importantly: use parameters!

[penagate]

Also (while I'm on my soapbox), the injection example given in the article is a stacked query:

DO this; DO that

The sidebar note points out that the mysql_query function does not execute these — so you might feel you are safe if you use this function.

However, there is a simpler form of injection. Consider this query:

DELETE FROM customers WHERE name="$name"

To wipe out the customers table, simply supply the following string for $name:

" OR 1 --

The resulting query is thus:

DELETE FROM customers WHERE name="" OR 1 --"

The comment symbol (--) is used to ensure the final quote is not parsed. This query will wipe out all records in the customers table.

[sridharao]

One of the biggest risk a website can face is by way of stacked queries where a table or database can be deleted/dropped. Since MySQl prevents this, the risk of tables or database being deleted/dropped is not there. Other kinds of injections might result in query failure and this probably is not serious unless the structure of codes show up.

Since delete statements are usually made after allowing the user the click on check boxes or pull down lists, the following code (subsequent to injection) is becomes obsolete.

DELETE FROM customers WHERE name="" OR 1 --"

Am I right about these?

What else should I be wary of?

[visualAd]

One of the biggest risk a website can face is by way of stacked queries where a table or database can be deleted/dropped. Since MySQl prevents this, the risk of tables or database being deleted/dropped is not there. Other kinds of injections might result in query failure and this probably is not serious unless the structure of codes show up.

Since delete statements are usually made after allowing the user the click on check boxes or pull down lists, the following code (subsequent to injection) is becomes obsolete.
Am I right about these?

What else should I be wary of?

Yes, you should be worried. Not all authenticated users are nice.

In addition, you can still use UNIONS, sorts and subqueries. The can threaten the confidentiality of your data and if the Mysl login youare using for you site happens to be the root login (which I am sure it is not ) then your users could look at data from other databases or grab user account information.

[penagate]

Since delete statements are usually made after allowing the user the click on check boxes or pull down lists, the following code (subsequent to injection) is becomes obsolete.
[...]
Am I right about these?

Any vulnerability should be addressed irrespective of how it may be exploited. I am not sure what you mean by "becomes obsolete". The example is valid in the context of the vulnerability being discussed, which is SQL injection.

There is a fundamental maxim of client/server programming to observe here: Never trust user input!

Again, use parameters and you can avoid this problem altogether.

Here is an example using PDO.

$st = $db->prepare('DELETE FROM customers WHERE name=?');
$st->execute($_POST['customer_name']); 

Assuming you have already checked that the user has supplied sufficient credentials to be authorised to do this, this is safe and secure code.

[sridharao]

Thanks for the replies.

If I know that magic_quotes are off in the setting, then there is no point in adding stripslashes to the $_POST. In such cases one must include addslashes. Right?

When mysql_real_escape_strings() are used, it automatically adds a slash to the quotes.


Casting the variable to the expected one is a good idea (as suggested in the article). I have an issue here:
The user enters a value of 200.00 in the text field in the form,

echo $_POST['amt']; //outputs 200.00 

but when I cast the same as double or float, the decimals are gone.

echo (float) $_POST['amt']; // outputs 200
echo (double) $_POST['amt']; // outputs 200 

Any idea where am I going wrong?

[visualAd]

The number 200 is the same as 200.00 and in floating point notation it is the same as 199.999999997 and 200.00000000001. If you want to change the format of number when displayed you use the number_format function.

[penagate]

If I know that magic_quotes are off in the setting, then there is no point in adding stripslashes to the $_POST. In such cases one must include addslashes. Right?

When mysql_real_escape_strings() are used, it automatically adds a slash to the quotes.

I am having a hard time following what you are saying. I apologise if we have confused you. You certainly should not be using both addslashes and mysql_real_escape_string.

As I have said before, the preferred method is to use parameters, as in my example. When you use parameters, you do not have to escape any data.

[KiwiDexter]

While on the subject of adding slashes to escape characters, what's the function to remove slashes when you want to display the date on a web page?

[penagate]

Can you give an example of the effect you are after?

[KiwiDexter]

Can you give an example of the effect you are after?

Would add slashes to avoid a MySQL error so I guess O'Connor would become O\'Connor. When I read the record wouldn't it then print O\'Connor rather than O'Connor? Not quite up to displaying data yet, still working on the input form

[penagate]

The escape sequences are part of the query syntax.

This query:

INSERT INTO customers ("Patrick O'Connor")

is equivalent to this query:

INSERT INTO customers ('Patrick O\'Connor')

Both insert the value Patrick O'Connor into the database.

Again (I will keep saying this until everyone does it) if you use parameters you can avoid this silliness altogether:

$st = $db->prepare('INSERT INTO customers (?)');
$st->execute("Patrick O'Connor"); 

In this example, no matter what the argument to the $st->execute call is — it could be taken directly from $_POST — the semantics of the query cannot be changed by rogue characters. There is no vulnerability to SQL injection.

[sridharao]

Penagate, thanks for the reply posts.

The purpose of adding slashes is to escape the quotes, which may be used by hackers to modify the query. Slashes can be added by either of these two means; addslashes() or mysql_real_escape_string(). The former performs as a function of PHP and the latter performs after database connection is established. Addslashes need not be explicitly used when magic_quotes_gpc is ON because automatically all quotes are addslash'ed. It is recommended NOT to set magic_quotes_gpc to ON or rely on addslashes(). In what way mysql_real_escape_string() is more secure than addslashes() when they perform the same action?

What I meant in my earlier post is that when magic_quotes are OFF, the quotes are NOT automatically escaped. If I am not using mysql_real_escape_string(), then addslashes() must be used. But if I am using mysql_real_escape_string(), then there is no need to use addalshes().

What I don't understand is why do people use stripslashses() on $_POST before including it in the query?
$query = "INSERT INTO mytable values (what) values ('".stripslashes($_POST['whatever']).""";
Doesn't this undo the whole purpose of escaping the quote and any appended code?

You stated that

INSERT INTO customers ("Patrick O'Connor") or INSERT INTO customers ('Patrick O\'Connor') both insert insert the value Patrick O'Connor into the database.

Then why escape the quote?

The concept of prepared statments are new, I think it requires php >5 and mysql 4.1 and is not available to lower versions. For those interested to know more about this, follow this link http://dev.mysql.com/tech-resources/...tatements.html

[KiwiDexter]

The escape sequences are part of the query syntax.

This query:

INSERT INTO customers ("Patrick O'Connor")

is equivalent to this query:

INSERT INTO customers ('Patrick O\'Connor')

Both insert the value Patrick O'Connor into the database.

Again (I will keep saying this until everyone does it) if you use parameters you can avoid this silliness altogether:

$st = $db->prepare('INSERT INTO customers (?)');
$st->execute("Patrick O'Connor"); 

In this example, no matter what the argument to the $st->execute call is — it could be taken directly from $_POST — the semantics of the query cannot be changed by rogue characters. There is no vulnerability to SQL injection.

Well you have my attention at least. So use of the double quote in a sql statement means you don't need to escape the O'Connor style text?

Secondly, and liking the idea of prepared statements using parameters, would this mean instantiating a class to hold the prepared statements, assuming that would be in an include file holding a number of similar prepared statements?

Dude must check this out over the weekend you may have just helped design our app for us (We were toying with classes and includes and the like).

[kows]

You stated that

INSERT INTO customers ("Patrick O'Connor") or INSERT INTO customers ('Patrick O\'Connor') both insert insert the value Patrick O'Connor into the database.

Then why escape the quote?

you must escape the second INSERT statement, or it would break. the first INSERT statement opens the string value with double quotes, meaning the next unescaped double quote ends the string. MySQL treats strings the same way PHP would:

$myvar = "this string ends here.";
$myvar .= "if I want to put a double quote in this string, I must \"escape\" it.";
$myvar .= 'or, I can open the string with single quotes and not need to "escape" it.';
$myvar .= 'still, I must \'escape\' single quotes, so that PHP knows when my string ends.'; 

so, if you had the following SQL statement:

INSERT INTO table VALUES('Patrick O'Connor')

MySQL would think that the string ended at O', thus requiring you to escape it, turning it into:

INSERT INTO table VALUES('Patrick O\'Connor')

or, using a different quote to open the string:

INSERT INTO table VALUES("Patrick O\'Connor")

lastly, you only perform stripslashes() on variables when magic_quotes_gpc is enabled. stripslashes() essentially reverses the effect. the process would work like this, and not like in the example you made:

//for portability, make sure magic_quotes_gpc is enabled
if(get_magic_quotes_gpc()){
  $myvar = stripslashes($_POST['myvar']);
}
$myvar = mysql_real_escape_string($_POST['myvar']); 

this will ensure that no "extra" (and unwanted) slashes are added to the database, but that the string is still safely escaped, as relying on magic_quotes_gpc is a bad idea.

oh, and I've always used mysql_real_escape_string() rather than addslashes() because this function was made for escaping strings in a MySQL connection. the descriptions of both functions look like they'd accomplish the same thing, although mysql_real_escape_string() does specifically escape actual characters that addslashes() does not.

[penagate]

The concept of prepared statments are new, I think it requires php >5 and mysql 4.1 and is not available to lower versions. For those interested to know more about this, follow this link http://dev.mysql.com/tech-resources/...tatements.html

As I mentioned in post #6 of this thread:
— If using MySQL you can use mysqli which is a data access library specifically for MySQL and is supported on PHP 4 and 5.
— If using PHP 5 you can using PDO which is a data access library which supports various database sources including MySQL, Oracle, Postgres and more.
— If using PHP 4 you can use MDB2 which is a PEAR extension with similar functionality to PDO.

Any new development and deployment really should be done in a PHP 5 environment. PHP 4 is antiquated and reached its end-of-life last year. (Or was it the year before? I forget.)

Well you have my attention at least. So use of the double quote in a sql statement means you don't need to escape the O'Connor style text?

Yes, but you would need to escape double quotes instead. The problem is really not addressed.

Secondly, and liking the idea of prepared statements using parameters, would this mean instantiating a class to hold the prepared statements, assuming that would be in an include file holding a number of similar prepared statements?

That is one approach. There are several I have used in the past, depending on how many application tiers you are willing to cope with:
— You could execute all SQL statements through a class which performs a lookup against the prepared statement objects, creating them if they do not yet exist; or
— create functions (or iterators, if you want to get funky) to perform data access, which may or may not then go through a data access class; or
— store statement objects in each class which requires database access; or simply
— use an include with all the statements declared globally. (Ugh).

I think the second technique is my favourite... it means you can write code which performs implicit SQL queries, like this:

# SELECT * FROM customers
foreach ($customers as $name => $customer)
{
  # SELECT COUNT(*) FROM orders WHERE customerid=?
  print $name.' has made '.count($customer->orders).' orders.';
} 

Some might say that such implicitness is evil, but I prefer to think it is concise and self-documenting.


Edit: Just realised after I posted that last example isn't very good... that result ought to be accomplished using only one query. (SELECT c.name, COUNT(o.*) FROM customers c LEFT JOIN orders o ON o.customerid=c.customerid.) Proof I haven't yet had my morning coffee.

[sridharao]

Thank you so much for all your posts. I tried out various combinations to have better understanding. This may be of some use to anyone who might stumble upon this thread. The following apply only when the magic_quotes_gpc is ON. This means PHP atuomatically escapes any quotes.

The value (val) entered by the user is Roja's "Flower Shop"

echo $_POST['val'];     //   Roja\'s \"Flower Shop\"
echo stripslashes(echo $_POST['val']);    //  Roja's "Flower Shop"
echo mysql_real_escape_string($_POST['val'])    // Roja\\\'s \\\"Flower Shop\\\"
echo mysql_real_escape_string(stripslashes($_POST['val']))     //  Roja\'s \"Flower Shop\" 

Now, lets see what would happen if these values were inserted into the table.

$raw= $_POST['val'];
$strip = stripslashes($_POST['val']);
$mys = mysql_real_escape_string($_POST['val']);
$bth = mysql_real_escape_string(stripslashes($_POST['val']));

insert into mytable(name) values ('$raw');    // Roja's "Flower Shop"     as user entered, but very risky
insert into mytable(name) values ('$strip');    // generates mysql error     syntax fails
insert into mytable(name) values ('$mys');    // Roja\'s \"Flower Shop\"    slashes persist everywhere
insert into mytable(name) values ('$bth');        // Roja's "Flower Shop"    perfect, just as user entered and safe 

So, I concur that if magic_quotes_gpc is ON in your server settings, it is best to use

mysql_real_escape_string(stripslashes($_POST['val'])) 

Now, coming to an article at http://www.tizag.com/mysqlTutorial/m...-injection.php
I tried out injecting the code by typing 'OR 1' in the form's text box,
When magic_quotes_gpc is ON, the syntax generated is

select name  from mytable where name ='\' OR 1\''     // this results in no records being fetched 

If stripslashed()

select name from mytable where name ='' OR 1''     // exactly as mentioned in the website 

, but this generates an sql syntax error. However, when I type out the sql in myphpadmin as
select name from mytable where name ='' OR 1 // all records are fetched.
I tried out by using

mysql_real_escape_string(stripslashes($_POST['val']) 

, yet injection fails.
However, if I modify the inection from ' OR 1' to ' OR 1 --' the dynamics change. When magic_quotes_gpc is ON, the syntax

select name from mytable where name ='\' OR 1 --\'' 

results in no records but if stripslashes are applied to it the syntax becomes

select name from mytable where name ='' OR 1 --'' 

and all the records are fetched, a successful sql injection.

The moral of the story is that it is vital to know if magic_quotes_gpc is set ON or not. If it is ON, NEVER use stripslashes alone.

As for prepared statements, I shall implement it soon.

[penagate]

Good work! I am glad you have absorbed something from this thread.