SQL Injection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f281536d",
   "metadata": {},
   "source": [
    "# SQL Injection\n",
    "\n",
    "In this notebook, we show a simple SQL injection example in which we inject and execute arbitrary SQL code in the database. Afterwards, we discuss the state-of-the-art solution to prevent SQL injections.\n",
    "\n",
    "Copyright Joris Nix & Jens Dittrich, [Big Data Analytics Group](https://bigdata.uni-saarland.de/), [CC-BY-SA](https://creativecommons.org/licenses/by-sa/4.0/legalcode)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b31428e",
   "metadata": {},
   "source": [
    "## Setup "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5962ea80",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg\n",
    "\n",
    "dsn = 'dbname=postgres user=postgres host=db'\n",
    "\n",
    "uname_pw = [('immanuel','ThisIsImmi'),\n",
    "            ('joris', 'bestpwintheworld.'),\n",
    "            ('kai', 'secretstr1ng'),\n",
    "            ('felix', 'gueswaht?'),\n",
    "            ('lukas', 'youll_never_know'),\n",
    "            ('marcel', 's3cby0psc'),\n",
    "            (\"' OR '1'='1\", 'nopassword')]    \n",
    "\n",
    "def init_db(dsn):\n",
    "    with psycopg.connect(dsn) as conn:\n",
    "        \n",
    "        # Open a cursor to perform database operations\n",
    "        with conn.cursor() as cur:\n",
    "\n",
    "            # Drop table if existing\n",
    "            cur.execute(query=\"DROP TABLE IF EXISTS users;\")\n",
    "\n",
    "            # Create accounts table\n",
    "            cur.execute(query=\"\"\"CREATE TABLE users\n",
    "                                 (id SERIAL PRIMARY KEY,\n",
    "                                 username varchar(42) UNIQUE,\n",
    "                                 pwd char(128));\"\"\")\n",
    "\n",
    "            # Insert sample data into accounts table\n",
    "            cur.executemany(\"\"\"INSERT INTO users(username, pwd) VALUES (%s, %s);\"\"\",\n",
    "                            ((u, p) for u, p in uname_pw))\n",
    "\n",
    "conn = psycopg.connect(dsn)\n",
    "conn.autocommit = True\n",
    "\n",
    "init_db(dsn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82c064fd",
   "metadata": {},
   "source": [
    "## SQL Injection Example\n",
    "\n",
    "First, we define a function that queries the database for information about a user with the name `username`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f913b84f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def execute_query(query):\n",
    "    cur = conn.cursor() # open cursor to perform db operation\n",
    "    cur.execute(query) # execute query\n",
    "    res = cur.fetchall() # fetch results\n",
    "    print(res)\n",
    "    \n",
    "def get_user_info(username):\n",
    "    statement = \"SELECT * FROM users WHERE username = '\" + username + \"'\";\n",
    "    execute_query(statement)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c76f46e2",
   "metadata": {},
   "source": [
    "We expect the user to enter something like this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "de94f208",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(6, 'marcel', 's3cby0psc                                                                                                                       ')]\n"
     ]
    }
   ],
   "source": [
    "username = \"marcel\" \n",
    "    \n",
    "get_user_info(username)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40c9886c",
   "metadata": {},
   "source": [
    "However, the user can also enter something like this, which should be a valid user in our system."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e7bbdbda",
   "metadata": {},
   "outputs": [],
   "source": [
    "username = \"' OR '1'='1\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3123af7d",
   "metadata": {},
   "source": [
    "This constructs the following query,\n",
    "```\n",
    "SELECT * FROM users WHERE username = '' OR '1'='1'\n",
    "```\n",
    "which results in a WHERE clause that is always true and therefore, returns the complete `users` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2a303372",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'immanuel', 'ThisIsImmi                                                                                                                      '), (2, 'joris', 'bestpwintheworld.                                                                                                               '), (3, 'kai', 'secretstr1ng                                                                                                                    '), (4, 'felix', 'gueswaht?                                                                                                                       '), (5, 'lukas', 'youll_never_know                                                                                                                '), (6, 'marcel', 's3cby0psc                                                                                                                       '), (7, \"' OR '1'='1\", 'nopassword                                                                                                                      ')]\n"
     ]
    }
   ],
   "source": [
    "get_user_info(username) # prints the whole table content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a604ffb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# or even this\n",
    "username = \"'; DROP TABLE users; SELECT 42 WHERE '42'='42\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e68989b",
   "metadata": {},
   "source": [
    "This constructs the following query.\n",
    "```\n",
    "SELECT * FROM users WHERE username = 'bla'; DROP TABLE users;\n",
    "SELECT 42 WHERE '42' = '42';\n",
    "```\n",
    "After executing this query, the table `users` and all its content is deleted from the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f709e16a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "get_user_info(username)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d23ed9c",
   "metadata": {},
   "source": [
    "We can check that the `users` table was indeed deleted by trying to retrieve the tuples from the table afterwards, which results in a `UndefinedTable` exception."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "373fa0e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "relation \"users\" does not exist\n",
      "LINE 1: SELECT * FROM users;\n",
      "                      ^\n"
     ]
    }
   ],
   "source": [
    "statement = \"SELECT * FROM users;\"\n",
    "try:\n",
    "    execute_query(statement)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd327dbc",
   "metadata": {},
   "source": [
    "# Prevent Attack using Prepared Statements\n",
    "\n",
    "The underlying problem and the reason that makes SQL Injections possible is the mixing of code and data. Therefore, SQL Injections can be prevented by sending the data (user input) separately from the SQL code to the database server.\n",
    "\n",
    "In PostgreSQL, this can be achieved by using so called [prepared statements](https://www.postgresql.org/docs/13/sql-prepare.html). Prepared statements allow us to define parameters and their datatype before executing a parameterized query. In the example from above the prepared statement would look as follows:\n",
    "```SQL\n",
    "PREPARE get_user_info(text) AS\n",
    "    SELECT *\n",
    "    FROM users\n",
    "    WHERE username=$1\n",
    "EXECUTE get_user_info(username);\n",
    "```\n",
    "This will make sure that when executing `get_user_info()` the entire user provided username is interpreted as string and the code and data are send separately to the database server.\n",
    "\n",
    "\n",
    "Below, we define a secure function to query the database for user information using a prepared statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8ac1b61d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_user_info_prepared(username):\n",
    "    # open cursor to perform db operation\n",
    "    cur = conn.cursor()\n",
    "    # we provide the user input as a parameter and set the `prepare` flag to True\n",
    "    cur.execute(query=\"SELECT * FROM users WHERE username=%s;\", params=(username,), prepare=True)\n",
    "    res = cur.fetchall()\n",
    "    print(res)\n",
    "\n",
    "# initialize database again (`users` table previously deleted)\n",
    "init_db(dsn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40d71bad",
   "metadata": {},
   "source": [
    "When executing the secure function using a valid username, we receive the expected output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "999bbff7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(5, 'lukas', 'youll_never_know                                                                                                                ')]\n"
     ]
    }
   ],
   "source": [
    "get_user_info_prepared(\"lukas\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e0e7f42",
   "metadata": {},
   "source": [
    "However, if we now try to get information for the user `' OR '1'='1` (or inject malicious code into the query), the username is interpreted as a string and therefore, the correct user information is returned (beforehand the whole table was returned)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "325f6952",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(7, \"' OR '1'='1\", 'nopassword                                                                                                                      ')]\n"
     ]
    }
   ],
   "source": [
    "get_user_info_prepared(\"' OR '1'='1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e30ad2d",
   "metadata": {},
   "source": [
    "It is also not possible to delete the `users` table anymore. The malicious code `DROP TABLE users;` is **not** executed. The user `'; DROP TABLE users; SELECT 42 WHERE 42='42` does not exist in the table `users` and therefore, an empty result is returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "deeb0ca9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "get_user_info_prepared(\"'; DROP TABLE users; SELECT 42 WHERE 42='42\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb9fd882",
   "metadata": {},
   "source": [
    "We can verify this by querying the `users` table afterwards."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b1cdfc42",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'immanuel', 'ThisIsImmi                                                                                                                      '), (2, 'joris', 'bestpwintheworld.                                                                                                               '), (3, 'kai', 'secretstr1ng                                                                                                                    '), (4, 'felix', 'gueswaht?                                                                                                                       '), (5, 'lukas', 'youll_never_know                                                                                                                '), (6, 'marcel', 's3cby0psc                                                                                                                       '), (7, \"' OR '1'='1\", 'nopassword                                                                                                                      ')]\n"
     ]
    }
   ],
   "source": [
    "execute_query(\"SELECT * FROM users;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db78cd48",
   "metadata": {},
   "source": [
    "**Note:** The `psycopg` PostgreSQL database adapter does not use the SQL statements `PREPARE` and `EXECUTE` internally. Instead it uses [\"protocol level commands such as the ones exposed by `PQsendPrepare`, `PQsendQueryPrepared`\"](https://www.psycopg.org/psycopg3/docs/advanced/prepare.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23803933",
   "metadata": {},
   "source": [
    "## Excursion: psycopg Placeholder vs Prepared Statements\n",
    "\n",
    "The definition of our `get_user_info_prepared()` query using prepared statements does not only set the `prepare` flag to `True` but also uses the `%s` placeholder in the query string to indicate that the parameter is a string.\n",
    "This already separates the user input from the SQL code and prevents the SQL injection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "954ef6ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_user_info_placeholder(username):\n",
    "    # open cursor to perform db operation\n",
    "    cur = conn.cursor()\n",
    "    # we provide the user input as a parameter and set the `prepare` flag to True\n",
    "    cur.execute(query=\"SELECT * FROM users WHERE username=%s;\", params=(username,), prepare=False)\n",
    "    res = cur.fetchall()\n",
    "    print(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5baa817c",
   "metadata": {},
   "source": [
    "Therefore, the following query correctly returns the information for user `' OR '1'='1`. Note, that we set the `prepare` flag to `False` in the above definition of the function `get_user_info_placeholder`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8d3156c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(7, \"' OR '1'='1\", 'nopassword                                                                                                                      ')]\n"
     ]
    }
   ],
   "source": [
    "get_user_info_placeholder(\"' OR '1'='1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "270bc3b1",
   "metadata": {},
   "source": [
    "Prepared statements are not only used to prevent SQL injections but also to optimize query execution by avoiding the re-compilation of the query (the query is cached) in case the same query (even with different parameters) is send multiple times to the database server.\n",
    "\n",
    "If we use the `psycopg` database adapter, it is sufficient to separate the input data from the SQL code by using placeholders. By setting the `prepare` flag to `True`, we can also increase efficieny by caching the plan.\n",
    "\n",
    "**Note:** When using PostgreSQL natively, we have to use prepared statements (SQL keywords `PREPARE` and `EXECUTE` as shown before) to separate the input data from the SQL code."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}