Prevent SQL Injection

PHP
1

We’ve had some issues with what I believe to be SQL injection attacks recently. From what I can tell, the best way to prevent SQL injection would be to use mysql_real_escape_string(). Am I correct or still naive in that assumption?

2

The best way to prevent SQL injection is to use prepared statements using either mysqli_ or PDO.
That being said, mysql_real_escape_string should also go a long way in preventing SQL injection but there are known attacks on it that work.

3

So I would imagine that, aside from using PDO, this is about as tight as it gets?

	//Function to sanitize values received from the form. Prevents SQL injection
	function clean($str) {
		$str = @trim($str);
		if(get_magic_quotes_gpc()) {
			$str = stripslashes($str);
		}
		return mysql_real_escape_string($str);
	}
	
	//Sanitize the POST values
	$login = clean($_POST['login']);
	$password = clean($_POST['password']);
4

I’d say so, yes :slight_smile:

5

Son of a…

[grumbles to go figure out what PDO actually is and how to use it]

Thanks for your help!

6

Mostly. The mysql_real_escape_string function should not be used as a part of fetching the initial variables. Instead that should be performed as a part of building the SQL string, as is shown in Example #2 at the mysql_query page.

7

What’s the difference between doing this:

$login = mysql_real_escape_string($_POST["login"]);

and this:

$query = sprintf("SELECT firstname, lastname, address, age FROM friends WHERE firstname='%s' AND lastname='%s'",
    mysql_real_escape_string($firstname),
    mysql_real_escape_string($lastname));

The same data is being passed through the same PHP function.

8

One reason is that a database connection is required before you can use mysql_real_escape_string.

A better reason though is that it’s entirely possible to modify strings, so that the strings going to the database are different from how they were retrieved at the start. By performing mysql_real_escape_string while you’re building the SQL string, you ensure that many more potential issues are satisfactorily handled.

Other info about preventing SQL Injection can be found at PHP’s SQL Injection page.

9

Point taken. Sorry - I just don’t like to take statements at face value when they don’t make sense to me. Thanks for the advice and explanation!

10

Another thing that seems to rarely get mentioned is revoke all permissions besides select. In most cases this can be done for people who aren’t in a certain permissions group because they shouldn’t be deleting, updating or inserting anything. Though its not always entirely possible it will make things more secure rather than using a single user for everyone with all permissions granted. In most cases you can revoke drop for everyone. This doesn’t prevent injection but makes makes certain attempts that get through irrelevant.

11

Yes, that’s an improtant issue. It’s the first item on the list of avoiding techniques from that PHP page about [url=“http://us.php.net/security.database.sql-injection”]SQL Injection

12

If you want to prevent your site from SQL Injection with help of Some Steps, Which are given below.

  • Never trust input
  • Craft error messages carefully
  • Patch and harden databases
  • Limit database privileges

There are also some tools to mitigate SQL injection attack.

  • Scrawlr
  • UrlScan v3.0 Beta
  • MSCASI
13

That is interesting, I wasn’t aware there could be some problems with mysql_real_escape_string in some multi-byte encodings. However, it looks like we can avoid them by using mysql_set_charset or mysqli_set_charset just after connecting. I always used direct SET NAMES query, which seems to be the less secure solution.

However, PDO doesn’t seem to have an equivalent of mysql_set_charset so its PDO::quote() method may never be as secure as mysql_real_escape_string (with mysql_set_charset) in certain environments. The only way would be prepared statements.

Here is the question then: how can I check and be sure that a PDO query uses native prepared statements and not emulated?

14

PDO will switch to emulated prepared statements if the driver doesn’t support native prepared statements. MySQL does support those so for MySQL you get native prepared statements (although some claim these are buggy …)

15

Yes, I’ve read this, what I meant was some way to find out on the db server side whether a query uses a prepared statement - regardless of what kind of client connects to it. So I’m wondering if this can be verified on the server side - not in php. Would mysql query log work for this?