{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cuando el dato lo escribe un desconocido\n",
    "\n",
    "La consulta que devuelve la tabla entera porque alguien escribió una comilla, y las dos líneas que lo arreglan.\n",
    "\n",
    "Cuaderno de práctica del capítulo 13 de **SQL desde cero**, de Miss Yera.\n",
    "\n",
    "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n",
    "\n",
    "Capítulo completo: https://missyera.com/guias/sql-desde-cero/inyeccion-sql/\n",
    "\n",
    "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n",
    "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n",
    "rato antes de abrirlo 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Se baja la base y se deja lista una función `q()` que corre las consultas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import urllib.request\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n",
    "con = sqlite3.connect(\"tienda.db\")\n",
    "\n",
    "def q(sql):\n",
    "    \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n",
    "\n",
    "    Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n",
    "    traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n",
    "    \"\"\"\n",
    "    resultado = None\n",
    "    trozo = \"\"\n",
    "    for linea in sql.splitlines(keepends=True):\n",
    "        trozo += linea\n",
    "        if sqlite3.complete_statement(trozo):\n",
    "            if trozo.strip():\n",
    "                cur = con.execute(trozo.strip())\n",
    "                resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                          columns=[d[0] for d in cur.description])\n",
    "                             if cur.description else None)\n",
    "            trozo = \"\"\n",
    "    if trozo.strip():\n",
    "        cur = con.execute(trozo.strip())\n",
    "        resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                  columns=[d[0] for d in cur.description])\n",
    "                     if cur.description else None)\n",
    "    con.commit()\n",
    "    return resultado\n",
    "\n",
    "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hasta aquí las consultas las escribías tú. En una aplicación de verdad, parte\n",
    "de la consulta la escribe **quien está del otro lado** 🔒\n",
    "\n",
    "Alguien teclea un nombre en un buscador, y ese nombre entra en tu\n",
    "`WHERE`. Ahí empieza el capítulo.\n",
    "\n",
    "Una pregunta antes: **¿qué pasa si lo que teclea esa persona lleva una\n",
    "comilla?** Piénsalo un segundo, porque la respuesta es todo lo que hay\n",
    "que entender de esto 💭"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La búsqueda normal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id, nombre FROM clientes WHERE nombre = 'Autoservicio Norte 007';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un cliente. Es lo que esperas, y lo que esperas es lo que va a pasar el 99% de\n",
    "las veces 🙂\n",
    "\n",
    "Pero fíjate en cómo está armada esa consulta si la escribe un programa: hay\n",
    "una parte fija y un trozo que vino de fuera, metido entre comillas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "SELECT id, nombre FROM clientes WHERE nombre = '   AQUÍ VA LO QUE TECLEÓ   ';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Y ahora la misma consulta, con una comilla dentro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Imagina que en vez de un nombre, la persona teclea esto:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "' OR '1'='1\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Métrelo en el hueco de arriba y lee despacio lo que queda, que es una consulta\n",
    "perfectamente válida:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS filas_devueltas FROM clientes WHERE nombre = '' OR '1'='1';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Los 120 clientes** 😳\n",
    "\n",
    "La comilla que tecleó cerró la que tu programa había abierto, y a partir de\n",
    "ahí lo que escribió dejó de ser un dato y pasó a ser *consulta*. El\n",
    "`OR '1'='1'` es cierto siempre, así que el `WHERE` deja\n",
    "pasar todo.\n",
    "\n",
    "Eso es la inyección SQL entera, y no hace falta saber nada más para hacerla.\n",
    "Por eso importa tanto 🚨"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Peor: llevarse una tabla que ni tocabas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT nombre FROM clientes WHERE nombre = ''\n",
    "UNION SELECT nombre FROM productos LIMIT 3;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esa consulta dice `FROM clientes` y está devolviendo\n",
    "**productos** 🤯\n",
    "\n",
    "El `UNION` del capítulo 8, que ahí era\n",
    "una herramienta, aquí es la puerta: quien inyecta lo usa para pegar los\n",
    "resultados de *otra* tabla a los de la tuya. Con paciencia se recorre la\n",
    "base entera, incluida la tabla de usuarios y contraseñas si la hay."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Y lo peor de todo: borrar"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Casi todos los conectores dejan mandar varias sentencias separadas por punto\n",
    "y coma. Vamos a montarlo sobre una tabla de mentira, para no romper la de\n",
    "verdad:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE TABLE promos_demo (id INTEGER PRIMARY KEY, texto TEXT);\n",
    "INSERT INTO promos_demo (texto) VALUES ('2x1 en abarrotes'), ('10% en bebidas'), ('envio gratis');\n",
    "SELECT COUNT(*) AS promos FROM promos_demo;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora, quien busca teclea esto:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "'; DELETE FROM promos_demo; --\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la consulta que queda es esta. Léela entera antes de ejecutarla:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT * FROM promos_demo WHERE texto = '';\n",
    "DELETE FROM promos_demo;\n",
    "SELECT COUNT(*) AS quedan FROM promos_demo;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cero 🫠\n",
    "\n",
    "La comilla cerró el dato, el punto y coma abrió una sentencia nueva, y los dos\n",
    "guiones del final comentan lo que sobraba para que no dé error de sintaxis. Tres\n",
    "caracteres y la tabla está vacía."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Todo esto pasa por una sola cosa: pegar texto de fuera dentro de una consulta."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El arreglo, que es una línea"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La solución no es revisar lo que teclea la gente ni prohibir las comillas.\n",
    "Alguien se puede apellidar D'Onofrio y tiene derecho a buscarse 🙂\n",
    "\n",
    "La solución es **no pegar el texto**: se manda la consulta con un\n",
    "hueco marcado y el dato aparte. Así la base sabe cuál es la consulta antes de\n",
    "ver el dato, y ese dato ya no puede convertirse en instrucciones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "con = sqlite3.connect(':memory:')\n",
    "con.execute('CREATE TABLE clientes (id INTEGER, nombre TEXT)')\n",
    "con.executemany('INSERT INTO clientes VALUES (?, ?)',\n",
    "                [(1, 'Bodega San Martin'), (2, 'Minimarket El Sol'), (3, 'Mayorista Peru')])\n",
    "\n",
    "def pegando(nombre):\n",
    "    sql = f\"SELECT nombre FROM clientes WHERE nombre = '{nombre}'\"\n",
    "    return sql, con.execute(sql).fetchall()\n",
    "\n",
    "def parametrizada(nombre):\n",
    "    return con.execute('SELECT nombre FROM clientes WHERE nombre = ?', (nombre,)).fetchall()\n",
    "\n",
    "ATAQUE = \"' OR '1'='1\"\n",
    "sql, filas = pegando(ATAQUE)\n",
    "print('la consulta que se arma:', sql)\n",
    "print('devuelve             :', len(filas), 'de 3 clientes')\n",
    "print('parametrizada devuelve:', len(parametrizada(ATAQUE)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La diferencia entre las dos funciones son **las comillas** 🎯\n",
    "\n",
    "La de arriba las pone ella con una f-string. La de abajo escribe un\n",
    "`?` y le pasa el dato en una tupla aparte, así que el motor lo trata\n",
    "como texto pase lo que pase. Con el mismo ataque devuelve 0, que es lo correcto:\n",
    "*ningún cliente se llama así*.\n",
    "\n",
    "Y no, no es más lento. Al contrario: muchos motores guardan el plan de la\n",
    "consulta con su hueco y lo reutilizan, que es justo lo que el capítulo\n",
    "14 quiere que pase."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El hueco para el dato, que se escribe distinto en cada uno\n",
    "\n",
    "| PostgreSQL | `cur.execute('... WHERE nombre = %s', (nombre,))` |\n",
    "|---|---|\n",
    "| MySQL | `cur.execute('... WHERE nombre = %s', (nombre,))` |\n",
    "| SQL Server | `cur.execute('... WHERE nombre = ?', (nombre,))` |\n",
    "| SQLite | `cur.execute('... WHERE nombre = ?', (nombre,))` |\n",
    "\n",
    "Ojo con el de PostgreSQL y MySQL: ese %s NO es el de las f-string de Python y no se rellena con % ni con format. Es el marcador del conector, y el dato va siempre en la tupla del segundo argumento. Escribirlo con % es exactamente el error del capítulo, con otra cara."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El error que sale sin que nadie ataque"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y antes de los ejercicios, la versión inocente del mismo fallo. Un cliente\n",
    "que se apellida así:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    SELECT nombre FROM clientes WHERE nombre = 'D'Onofrio';\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "OperationalError: near \"Onofrio\": syntax error\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ni ataque ni nada: un apellido normal. La comilla del apellido cierra la del\n",
    "programa y lo que sigue deja de tener sentido.\n",
    "\n",
    "Esto es lo que más veces vas a ver en la práctica, y es la **misma\n",
    "grieta** por la que entra lo demás. Si tu sistema se rompe con un\n",
    "apellido, el agujero ya está abierto: solo falta que alguien escriba algo peor\n",
    "que un apellido 🚩"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que NO sirve"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Lo que se suele intentar | Por qué no basta |\n",
    "|---|---|\n",
    "| Prohibir las comillas en el formulario | Deja fuera apellidos de verdad, y quien ataca manda la petición sin pasar por tu formulario |\n",
    "| Duplicar las comillas a mano | Funciona hasta el primer caso raro. Es reescribir mal lo que el conector ya hace bien |\n",
    "| Buscar palabras como DROP o DELETE | El ataque de arriba no lleva ninguna. Y las mayúsculas, los comentarios y los espacios raros lo esquivan |\n",
    "| Confiar en que la web es interna | La mitad de los casos que he visto son de gente de dentro, y no siempre a propósito |\n",
    "| Parametrizar el dato pero pegar el nombre de la columna | El hueco vale para datos, no para nombres de tabla o columna. Para eso hay que validar contra una lista tuya |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lo único que sirve es el hueco. Todo lo demás son parches encima de la\n",
    "grieta 🩹"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Un equipo arma el informe de ventas por ciudad. La ciudad la elige quien consulta, en un desplegable, así que \"no puede venir cualquier cosa\". El dato lo parametrizan bien y el orden lo pegan, porque un ORDER BY no acepta hueco.\n",
    "\n",
    "```\n",
    "# la ciudad viene de un desplegable y va parametrizada, correcto\n",
    "sql = \"SELECT ciudad, SUM(monto) AS total FROM pedidos p \"\n",
    "sql += \"JOIN clientes c ON c.id = p.id_cliente WHERE c.ciudad = ? \"\n",
    "sql += \"GROUP BY ciudad ORDER BY \" + columna + \" \" + sentido\n",
    "\n",
    "cur.execute(sql, (ciudad,))\n",
    "```\n",
    "\n",
    "**¿Qué está mal?** La respuesta está en el cuaderno de soluciones. Míralo tú primero."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Siete sobre `tienda.db`. El 4 es el que más enseña 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Cuántas filas debería devolver\n",
    "\n",
    "Antes de correr nada: si el buscador recibe\n",
    "`Mayorista Peru 019`, ¿cuántas filas salen? ¿Y con\n",
    "`' OR 1=1 --`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Cuenta las tablas de la base ajena\n",
    "\n",
    "Con un `UNION`, averigua cuántas tablas tiene la\n",
    "base desde una consulta que solo miraba clientes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. El apellido con comilla, arreglado\n",
    "\n",
    "Haz que la búsqueda de `D'Onofrio` funcione, sin\n",
    "prohibir nada."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Escribe tú el ataque\n",
    "\n",
    "Sin código nuevo. Tienes esta consulta y controlas\n",
    "`{correo}`. Escribe en un papel qué tecleas para entrar sin saber la\n",
    "contraseña."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Revisa código de verdad\n",
    "\n",
    "Sin dataset. Busca en el código de tu trabajo, o en\n",
    "cualquier proyecto tuyo, las tres señales de esto."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. El que borra, con red de seguridad\n",
    "\n",
    "Repite el `DELETE` encadenado, pero envuélvelo en\n",
    "una transacción y deshazlo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Los permisos, que son la otra mitad\n",
    "\n",
    "Sin código. Piensa qué permisos necesita de verdad el\n",
    "usuario con el que tu web se conecta a la base."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Estás revisando código y encuentras esta línea. ¿Qué haces?\n",
    "`cur.execute(\"SELECT * FROM pedidos WHERE id = %s\" % id_pedido)`\n",
    "\n",
    "a) La cambio a execute con el dato en el segundo argumento\n",
    "\n",
    "b) Está bien, ya usa %s\n",
    "\n",
    "c) Compruebo que id_pedido sea un número y lo dejo\n",
    "\n",
    "d) Le pongo comillas alrededor del %s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 🔓 Toda inyección sale de lo mismo: pegar texto de fuera dentro de la\n",
    "consulta.\n",
    "\n",
    "- 💥 Una comilla convirtió una búsqueda de un cliente en las 120 filas, y un\n",
    "`UNION` se llevó nombres de otra tabla.\n",
    "\n",
    "- 🔒 Se arregla con el hueco: `?` o `%s` según el motor, y\n",
    "el dato en el segundo argumento.\n",
    "\n",
    "- 📋 El hueco vale para datos. Nombres de columna y sentidos de orden se\n",
    "validan contra una lista tuya.\n",
    "\n",
    "- 🩹 Prohibir comillas, buscar palabras raras y confiar en que la red es\n",
    "interna no sirven.\n",
    "\n",
    "- 🛡️ Y si además el usuario de la aplicación no puede borrar tablas, el daño\n",
    "es mucho menor.\n",
    "\n",
    "Si esto lo vas a escribir desde Python, el conector y las tuplas están en el\n",
    "[libro de Python desde cero](https://missyera.com/guias/python-desde-cero/) 🐍\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 13 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/inyeccion-sql/\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}