App Design with SQL

Let’s look at using MySQL FROM Python

Security - SQL Injection

A SQL injection attack consists of insertion or “injection” of a SQL query via

A SQL injection attack consists of insertion or “injection” of a SQL query via

the input data FROM the client to the application. A successful SQL injection exploit can read sensitive data FROM the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. The Open Web Application Security Project (OWASP)

How does it work?

First-Order SQL Injection attack

Suppose we have an application that accepts a string FROM the user and then constructs a SQL query FROM it.

SELECT * FROM instructor WHERE name = '" + inputstring +"'"

The programmer expects the users to always cooperate and enter an instructor’s name.
The programmer also assumes that if it’s misspelled or not a known instructor’s name, the SQL query will fail. Thats great since the programmer has no idea what names are valid … they’re in the database!

The programmer decides no validity checking is needed.

Everything’s fine until user Eve enters the following instead of a name:

X' OR 'Y' = 'Y

Then the resulting statement becomes:

SELECT * FROM instructor WHERE name = '" + "X' or 'Y' = 'Y" + "'"

which is:

SELECT * FROM instructor WHERE name = 'X' or 'Y' = 'Y'

Eve could have been nice and responded with this instead:

X'; UPDATE instructor SET salary = salary + 10000;

For example:

mysql> SELECT * FROM instructor;
+-------+------------+------------+-----------+
| ID    | name       | dept_name  | salary    |
+-------+------------+------------+-----------+
| 10101 | Srinivasan | Comp. Sci. |  65000.00 |
| 12121 | Wu         | Finance    |  90000.00 |
| 12324 | foople     | Biology    | 123123.00 |
| 15151 | Mozart     | Music      |  40000.00 |
| 22222 | Einstein   | Physics    |  95000.00 |
| 32343 | El Said    | History    |  60000.00 |
| 33456 | Gold       | Physics    |  87000.00 |
| 45565 | Katz       | Comp. Sci. |  75000.00 |
| 58583 | Califieri  | History    |  62000.00 |
| 76543 | Singh      | Finance    |  80000.00 |
| 76766 | Crick      | Biology    |  72000.00 |
| 83821 | Brandt     | Comp. Sci. |  92000.00 |
| 98345 | Kim        | Elec. Eng. |  80000.00 |
+-------+------------+------------+-----------+
13 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT * FROM instructor WHERE name = 'Mozart';
+-------+--------+-----------+----------+
| ID    | name   | dept_name | salary   |
+-------+--------+-----------+----------+
| 15151 | Mozart | Music     | 40000.00 |
+-------+--------+-----------+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM instructor WHERE name = 'Mozart' OR 'Y'='Y';
+-------+------------+------------+-----------+
| ID    | name       | dept_name  | salary    |
+-------+------------+------------+-----------+
| 10101 | Srinivasan | Comp. Sci. |  65000.00 |
| 12121 | Wu         | Finance    |  90000.00 |
| 12324 | foople     | Biology    | 123123.00 |
| 15151 | Mozart     | Music      |  40000.00 |
| 22222 | Einstein   | Physics    |  95000.00 |
| 32343 | El Said    | History    |  60000.00 |
| 33456 | Gold       | Physics    |  87000.00 |
| 45565 | Katz       | Comp. Sci. |  75000.00 |
| 58583 | Califieri  | History    |  62000.00 |
| 76543 | Singh      | Finance    |  80000.00 |
| 76766 | Crick      | Biology    |  72000.00 |
| 83821 | Brandt     | Comp. Sci. |  92000.00 |
| 98345 | Kim        | Elec. Eng. |  80000.00 |
+-------+------------+------------+-----------+
13 rows in set (0.00 sec)

So if the following query is built into the program and Eve decides to be _very_ nice, she could ...

mysql> SELECT * FROM instructor
    -> WHERE name = 'X'; UPDATE instructor SET salary = salary + 10000;

Empty set (0.00 sec)

Query OK, 13 rows affected (0.03 sec)
Rows matched: 13  Changed: 13  Warnings: 0

mysql> SELECT * FROM instructor;
+-------+------------+------------+-----------+
| ID    | name       | dept_name  | salary    |
+-------+------------+------------+-----------+
| 10101 | Srinivasan | Comp. Sci. |  75000.00 |
| 12121 | Wu         | Finance    | 100000.00 |
| 12324 | foople     | Biology    | 133123.00 |
| 15151 | Mozart     | Music      |  50000.00 |
| 22222 | Einstein   | Physics    | 105000.00 |
| 32343 | El Said    | History    |  70000.00 |
| 33456 | Gold       | Physics    |  97000.00 |
| 45565 | Katz       | Comp. Sci. |  85000.00 |
| 58583 | Califieri  | History    |  72000.00 |
| 76543 | Singh      | Finance    |  90000.00 |
| 76766 | Crick      | Biology    |  82000.00 |
| 83821 | Brandt     | Comp. Sci. | 102000.00 |
| 98345 | Kim        | Elec. Eng. |  90000.00 |
+-------+------------+------------+-----------+
13 rows in set (0.00 sec)

Now we get the joke!

SLIDE 2

Or even this one: SLIDE 3

Second-Order SQL Injection attack (Excerpted FROM Oracle SQL Injection guidance)

You can send the results of a SELECT command into individual variables:

SELECT attribute1, attribute2, attribute3, ... INTO @variable1, @variable2, @variable3,... FROM tablename WHERE condition;

Now, suppose you have a Web-based application which stores usernames alongside other session information. Given a session identifier FROM a cookie you want to retrieve the current username and then use it retrieve information about that user. You might therefore have code for an “Update User Profile” screen somewhat similar to the following:

SELECT username FROM sessiontable WHERE session=session_id
  INTO session_username;

SELECT ssn FROM users WHERE username=session_username INTO session_ssn;

This will be injectable if the attacker had begun on the earlier “Create Account” screen by creating a username such as:

XXX' OR username='JANE

Which results in the query:

SELECT ssn FROM users WHERE username='XXX’ OR username='JANE'

If the user XXX does not exist, the attacker has successfully retrieved Jane’s social security number.

Protection against second-order attacks would require detection of the fake username or some other means of user input validation.

---

Prepared Statements in MySQL

MySQL Queries are sent to the server which has to parse them, execute them, and send back the results.

If a user is running an application on a server and making many similar queries, the use of server-side prepared statements can reduce the parsing load on the server.

Protection against first-order SQL Injection attacks

So now we have a way to protect our system FROM the user!

-- Instead of the vulnerable
SELECT * FROM instructor WHERE name = '" + inputstring +"'"
-- We can use PREPARed statements
-- Basic example
PREPARE stmt FROM 'SELECT * FROM instructor WHERE name=?';
SET @n='Mozart';
EXECUTE stmt USING @n;
-- and other mischief
SET @n="*";
EXECUTE stmt USING @n;
-- Now to defeat SQL injection!
SELECT * FROM instructor;
SET @n="X'; UPDATE instructor SET salary = salary + 10000";
EXECUTE stmt USING @n;
SELECT * FROM instructor;

mysql> PREPARE stmt FROM 'SELECT * FROM instructor WHERE name=?';
Query OK, 0 rows affected (0.03 sec)
Statement prepared

mysql> SET @n='Mozart';
Query OK, 0 rows affected (0.02 sec)

mysql> EXECUTE stmt USING @n;
+-------+--------+-----------+----------+
| ID    | name   | dept_name | salary   |
+-------+--------+-----------+----------+
| 15151 | Mozart | Music     | 46305.00 |
+-------+--------+-----------+----------+
1 row in set (0.02 sec)

mysql> -- and other mischief
mysql> SET @n="*";
Query OK, 0 rows affected (0.02 sec)

mysql> EXECUTE stmt USING @n;
Empty set (0.02 sec)

mysql> SET @n="X'; UPDATE instructor SET salary = salary + 10000";
Query OK, 0 rows affected (0.06 sec)

mysql> EXECUTE stmt USING @n;
Empty set (0.02 sec)

mysql> SELECT * FROM instructor WHERE name='Mozart';
+-------+--------+-----------+----------+
| ID    | name   | dept_name | salary   |
+-------+--------+-----------+----------+
| 15151 | Mozart | Music     | 50000.00 |
+-------+--------+-----------+----------+
1 row in set (0.00 sec)

mysql>

Other Defenses and prevention

Dont write vulnerable code!

FROM the OWASP recommendations:

1. Primary Defenses:

   a. Option #1: Use of Prepared Statements (Parameterized Queries).
   b. Option #2: Use of Stored Procedures.
   c. Option #3: Escaping all User Supplied Input.

2. Additional Defenses:

   a. Enforce Least Privilege.
   b. Use Known Good List Input Validation.

Using prepared statements to choose a table at runtime

You can use prepared statements to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable:

mysql> USE test;
mysql> CREATE TABLE t1 (a INT NOT NULL);
mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';
mysql> SET @s = CONCAT('SELECT * FROM ', @table);

mysql> PREPARE stmt3 FROM @s;
mysql> EXECUTE stmt3;
+----+
| a  |
+----+
|  4 |
|  8 |
| 11 |
| 32 |
| 80 |
+----+

mysql> -- Since the system may have set a limit to the number of
mysql> -- prepared statements that exist at once, you should clean up.
mysql> DEALLOCATE PREPARE stmt3;

Common query acceleration

Prepared statements can also be used to speed up heavily used SQL statements that are identical except for a few parts. For example, a plumbing company manager might send a daily query to the parts supplier databa se for tems needed for current jobs. Most of the data in the query would be his requestor id, zipcode (in case there are multiple warehouses and the nearest one should be prioritized), etc. The only parts of the query that vary are part number. They allow the SQL processor to essentially pre-compile most of the query and to just drop in the variable parts when the prepared statements are used.

-- get ready
PREPARE partsInquiry FROM
    'SELECT WarehouseZip, numberInStock FROM MasterInventory WHERE WarehouseZip=NearestTo(?) AND partNumber=?';

-- then used like this (probably FROM a web app)
SET @zip        = webpage.zip;
SET @partnumber = webpage.partnum;
EXECUTE partsInquiry using @zip, @partnumber;

Here’s a simpler example:

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1;
ERROR 1210 (HY000): Incorrect arguments to EXECUTE

mysql> EXECUTE stmt1 using @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)

Dynamic SQL

Leveraging both stored procedures and prepared statements you can build up an entire SQL query at runtime.

Here’s an example FROM Murach:

USE ap;

DROP PROCEDURE IF EXISTS SELECT_invoices;

DELIMITER //

CREATE PROCEDURE SELECT_invoices
(
  min_invoice_date_param   DATE,
  min_invoice_total_param  DECIMAL(9,2)
)
BEGIN
  DECLARE SELECT_clause VARCHAR(200);
  DECLARE WHERE_clause  VARCHAR(200);

  SET SELECT_clause = "SELECT invoice_id, invoice_number,
                       invoice_date, invoice_total
                       FROM invoices ";
  SET WHERE_clause =  "WHERE ";

  IF min_invoice_date_param IS NOT NULL THEN
    SET WHERE_clause = CONCAT(WHERE_clause,
       " invoice_date > '", min_invoice_date_param, "'");
  END IF;

  IF min_invoice_total_param IS NOT NULL THEN
    IF WHERE_clause != "WHERE " THEN
      SET WHERE_clause = CONCAT(WHERE_clause, "AND ");
    END IF;
    SET WHERE_clause = CONCAT(WHERE_clause,
       "invoice_total > ", min_invoice_total_param);
  END IF;

  IF WHERE_clause = "WHERE " THEN
    SET @dynamic_sql = SELECT_clause;
  ELSE
    SET @dynamic_sql = CONCAT(SELECT_clause, WHERE_clause);
  END IF;

  PREPARE SELECT_invoices_statement
  FROM @dynamic_sql;

  EXECUTE SELECT_invoices_statement;

  DEALLOCATE PREPARE SELECT_invoices_statement;
END//

DELIMITER ;

-- Now we test it - uncomment one of the following at a time

CALL SELECT_invoices('2014-07-25', 100);

-- CALL SELECT_invoices('2014-07-25', NULL);

-- CALL SELECT_invoices(NULL, 1000);

-- CALL SELECT_invoices(NULL, NULL);

---