Parametrizované SQL dotazy v PHP s MYSQLI

O napadení webu a zjištění hesel útočníky už toho bylo napsáno hodně. Dříve se doporučovalo escapovat nebo používat mysqli->real_escape_string() a na čísla intval(n) nebo floatval(n.nn). Navíc hesla do databáze ukládat jako hashe SHA512 a mít sloupec sůl. Před uložením hesla do databáze toto heslo ještě „osolit“ jedinečnou solí pro každý záznam.
To je sice zatím bezpečné, ale má to háček. Tím je lidský faktor a zapomenutí odescapovat proměnné nebo ošetřit čísla vstupující do SQL dotazu z formuláře. Pak se stane webová stránka zranitelná na SQL injektáž (SQL injection).

Proč používat parametrizovaný dotaz? Nejdůležitějším důvodem pro použití parametrizovaných dotazů je vyhnout se útokům s vložením SQL (SQL injection). Lidský faktor je chybující prvek, zapomenutí escapovat nějakou proměnnou může mít fatální následky. Lidé prostě dělají chyby, stroje ne. Za druhé, parametrizovaný dotaz se postará o scénář, kde by sql dotaz mohl selhat, např. vložení O'Baily do SQL dotazu.

Proto se nyní (rok 2022) doporučuje používat v PHP parametrizované SQL dotazy a ukládat hesla do databáze pomocí PHP funkce password_hash(). Ověřovat heslo pomocí sesterské funkce password_verify(). Více informací je v článku „bezpečné ukládání hesel do MariaDB“. Jako PHP rozhraní pro práci s MariaDB lze používat objektové PDO nebo již taky objektové MYSQLI.

Nejdříve si vytvoříme účet testik, databázi testik a tabulku uživatele v utf8 pomocí phpmysqladmin
(https://localhost/phpmyadmin/).

virhundo@hirunduv:~$ mariadb -u testik -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 151
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \C utf8
Charset changed
MariaDB [(none)]> use testik;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testik]> show tables;
+------------------+
| Tables_in_testik |
+------------------+
| uzivatele        |
+------------------+
1 row in set (0.001 sec)

MariaDB [testik]> describe uzivatele;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| uid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ucet  | varchar(15)      | NO   | UNI | NULL    |                |
| heslo | varchar(128)     | NO   |     | NULL    |                |
| prijm | varchar(25)      | NO   |     | NULL    |                |
| jmeno | varchar(25)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
5 rows in set (0.020 sec)

MariaDB [testik]> select * from uzivatele;
+-----+---------------+--------------------------------------------------------------+---------------+-----------+
| uid | ucet          | heslo                                                        | prijm         | jmeno     |
+-----+---------------+--------------------------------------------------------------+---------------+-----------+
|   1 | admin         | $2y$10$Zus3PC71xQit/2Bbu/esRO9nUGHHiUCyUASnPkO6mX3Ml0IngGtEm | Světlá        | Karolína  |
|   2 | šéfredaktor   | $2y$10$/mL7eGXPDTVHc9l5x/iVZeIowmOaDJg4th8UkcG21.DhxxZrdudyW | Pan           | Tau       |
|   3 | redaktor      | $2y$10$jiIi1oAy6Q4BQVuCZy2k/OWIEa66/xkqh450T.4kE78DQ7smViN4y | Novák         | Petr      |
|   4 | přispěvatel   | $2y$10$QmCpFjBgoV93mu9IeT0UKOoGt6NMMt5OZVJh4sMunmwwsRihNJvJu | Soros         | Georg     |
|   5 | poloosa       | $2y$10$Yi4tJ8KkFX6QOTKJR2HOVehSxbrNq6PVreiIf7Htwd.8SLPDCTynC | Kudeříková    | Marie     |
|   9 | traktor       | $2y$10$Jux8JT5NWoi7O5MNxb8dNe9PXo.qr7.FIgjUPjyr4jz9rmD94mxUC | Náhlovský     | Josef     |
|  11 | vizidlo       | $2y$10$D6vJtFZWv3MtrQhiVESGR.WVSrtrN6EXMl49jk/oMadZ1mVO0RulG | Pokorný       | Jan       |
+-----+---------------+--------------------------------------------------------------+---------------+-----------+
7 rows in set (0.022 sec)

Nyní se přihlásíme do konzolového klienta mariadb, viz výše. Vše by mělo fungovat tak, jak je to popsané. Máme připravenu databázi a tabulku „uzivatele“ k ukázce SQL injektáže (SQL injection).
V následující ukázce je již přihlašovací jméno, účet přednastavené. Je to ukázkový příklad neošetřeného PHP kódu proti SQL injektáži.

<?php
echo "<!DOCTYPE html>\n";
echo "<html lang=\"cs\">\n";
echo "<head>\n";
echo "<meta charset=\"utf-8\" />\n";
echo "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\" />\n";
echo "<meta name=\"robots\" content=\"all\" />\n";
echo "<meta name=\"keywords\" content=\"Parametrizované,SQL,dotazy,PHP,mysqli\" />\n";
echo "<meta name=\"description\" content=\"Parametrizované SQL dotazy v PHP rozhraní mysqli\" />\n";
echo "<meta name=\"autor\" content=\"Kocour\" />\n";
echo "<title>Parametrizované SQL dotazy v PHP rozhraní mysqli</title>\n";
echo "</head>\n";
echo "<body>\n";
echo "<h1>Parametrizované SQL dotazy v PHP rozhraní mysqli</h1>\n";
 
$mysqli = new mysqli("localhost", "testik", "testik", "testik");
if ($mysqli->character_set_name()!="utf8") { $mysqli->set_charset("utf8"); }

$submit="poslat";
// ============== přihlášení uživatele (admina) =============
if (!empty($_POST[$submit]) and !empty($_POST['ucet']) and !empty($_POST['heslo'])) {
  // tady načteme heslo z databáze a dáme do proměnné $hash_heslo
  $ucet = $_POST['ucet'];
 //$ucet = $mysqli->real_escape_string($ucet); // starší obrana proti SQL injection
 $dotaz = "select * from uzivatele where ucet=\"".$ucet."\"";
 //echo $dotaz."<br />";
 $vysledek = $mysqli->query($dotaz);
 $poczaznamu = $vysledek->num_rows;
 // ********** SQL injection na Zadej účet: " OR "1"="1 ************ !!!!!!!!!!!!!!!!!!!!!!    
 // vytvoří SQL dotaz: select * from uzivatele where ucet="" OR "1"="1"     !!! To vrátí všechny záznamy
 if ($poczaznamu > 0) {
 $zaznam = $vysledek->fetch_array(MYSQLI_ASSOC);
 $hash_heslo = $zaznam['heslo']; 
 $heslo2 = $_POST['heslo'];
 // porovnání hesel password_hash()-> password_verify()
 if (password_verify($heslo2, $hash_heslo)) {
 echo 'Správné heslo!';
 } else {
 echo 'Špatné jméno nebo heslo.<br />';
 echo "<pre>";
 echo "**********************************************************<br />";
 echo " ----- H A C K N U T O   !!! -------                      <br />";
 echo " nezapnutá obrana proti SQL injection !!!                 <br />";
 echo "**********************************************************<br />";
 echo "SQL dotaz: ".$dotaz."<br />";
 echo "+-----+---------------+--------------------------------------------------------------+---------------+-----------+<br>";
 echo "| uid | ucet          | heslo                                                        | prijm         | jmeno     |<br>";
 echo "+-----+---------------+--------------------------------------------------------------+---------------+-----------+<br>";
 echo "|   1 | admin         | &#36;2y&#36;10&#36;Zus3PC71xQit/2Bbu/esRO9nUGHHiUCyUASnPkO6mX3Ml0IngGtEm | Světlá        | Karolína  |<br>";
 echo "</pre>";
 }
 } else {
 echo 'Špatné jméno nebo heslo.<br />';
 }    
}
// ==========================================================

// ============================= přihlašovací formulář ================================================
// účet:admin
// heslo:tajne-heslo-milanku
echo "<form method=\"post\">\n";
echo "<fieldset><br />\n";
echo "<legend>Zadání autorizačních údajů</legend>\n";
echo "Zadej přihlašovací účet<br />";
// SQL injection na Zadej přihlašovací účet: " OR "1"="1
echo "<input type=\"text\" name=\"ucet\" value=\"&quot; OR &quot;1&quot;=&quot;1\" size=\"\" /><br /><br />\n";
echo "Zadej heslo<br />";
echo "<input type=\"password\" name=\"heslo\" value=\"123456\" size=\"\" /><br />\n";
echo "<br /><input type=\"submit\" name=\"".$submit."\" value=\"odeslat\" />\n";
echo "</fieldset>\n";
echo "</form>\n";
// ======================================================================================================

$mysqli->close();  // není nutné
echo "</body>\n";
echo "</html>\n"; 
?>

Zobrazí se nám přihlašovací formulář s předvyplněným hackerským jménem. Přitom heslo může být libovolné a přesto je obejit přihlašovací formulář. Přesto, že jsme měli heslo správně uloženo jako password_hash(), přesto že jsme použili správně password_verify(), hacker se bez ošetřeného kódu proti SQL injektáži k přihlašovacím údajům dostal.

Takovýto neošetřený dotaz z formuláře je skvělé místo pro SQL injektáž (injection). Místo očekávaného SQL dotazu: „select * from uzivatele where ucet="admin";“ byl dotaz pozměněn na:

select * from uzivatele where ucet="" OR "1"="1";

To hackerovi umožní obejít autorizační logiku a dodá všechny záznamy z tabulky uživatelé. Pokud ještě byly hesla uložena pomocí zastaralých MD5, SHA1 hashovacích funkcí, tak zná i jejich otevřený tvar a získá plnou kontrolu nad webovou aplikací.

Nyní už známe jak to nedělat.
Ve zdrojovém kódu by stačilo odremovat (zrušit // ) na řádku 24 zapnout obranu proti SQL injektáži (injection):
$ucet = $mysqli->real_escape_string($ucet);

Pak by byla aplikace zabezpečená starším a již nedoporučovaným způsobem.

Parametrizované dotazy v MYSQLi

Ale existuje modernější a doporučovaný způsob obrany (nebo ochrany) proti SQL injektáži (injection) - parametrizované dotazy v PHP. V parametrizovaných dotazech nevkládáme proměnné do dotazu ale dáváme zástupné znaky, obvykle otazníky. Proměnné předáme později najednou v poli. Bohužel oficiální dokumentace je značně chaotickým mixem zastaralých řešení.

Používáme objektové:

1. $mysqli->prepare($dotaz)
2. $stmt->bind_param($typy, ...$parametry)
3. $stmt->execute()

U bodu 2. je důležité a málo zdokumentované $stmt->bind_param($typy, ...$parametry), kdy jsou třeba už jen dvě proměnné, první typu string ($typy="ssssi"), druhá typu pole ($parametry=array($ucet,$heslo,$prijmeni,$jmeno,$uid)).
Už žádné zastaralé $stmt->bind_param('sssd', $code, $language, $official, $percent);bind_result(), call_user_func_array()... ale

$typy = "ssss";
$parametry = array($ucet,$heslo,$prijmeni,$jmeno);
...
$stmt->bind_param($typy, ...$parametry);

Jako typy parametrů (zde proměnná $typy) se používá:

• i (integer)
• d (double, real, float)
• s (string)
• b (blob)

Escapování proměnných u parametrizovaných dotazů již není potřeba ($mysqli->real_escape_string). To ale neznamená, že bychom je neměli vůbec ošetřovat. Minimálně u proměnných omezit délku, oříznutí počátečních a koncových mezer, strip_tags(), zbavit se znaků ", ', <, >.

SELECT

$ucet = $_POST['ucet'];
//$delka=15; $ucet = mb_substr(trim($_POST['ucet']),0,$delka,"utf-8"); $ucet=strip_tags($ucet); $ucet = filter_var($ucet, FILTER_SANITIZE_SPECIAL_CHARS);
$delka=15; $ucet = mb_substr(trim($_POST['ucet']),0,$delka,"utf-8"); $ucet=strip_tags($ucet);$ucet = str_replace(array("'",">","<",'"'), array("","","",""), $ucet);
// =================== parametrizovaný dotaz ===============  
$dotaz = "select * from uzivatele where ucet=?";
$typy = "s";
$parametry = array($ucet);
if ($stmt = $mysqli->prepare($dotaz)) { 
 $stmt->bind_param($typy, ...$parametry);
 $stmt->execute();
 $vysledek = $stmt->get_result();
 $stmt->close();
 $poczaznamu = $vysledek->num_rows;
 while($zaznam = $vysledek->fetch_assoc()) {
  echo $zaznam['jmeno']." ".$zaznam['prijm']."<br />";
 }
}  
// ==========================================================

Pokud používáme výhradně parametrizované SQL dotazy a chceme vložit dotaz bez proměnných, stačí vynechat ->bind_param().

// =================== parametrizovaný dotaz ===============  
$dotaz = "select jmeno,prijm from uzivatele";
if ($stmt = $mysqli->prepare($dotaz)) { 
   $stmt->execute();
   $vysledek = $stmt->get_result();
   $stmt->close();
   $poczaznamu = $vysledek->num_rows;
   while($zaznam = $vysledek->fetch_assoc()) {
     echo $zaznam['jmeno']." ".$zaznam['prijm']."<br />";
   }
}

INSERT

$ucet = mb_substr(trim($_POST['ucet']),0,15,"utf-8"); $ucet = htmlspecialchars($ucet, ENT_QUOTES);
$heslo = mb_substr(trim($_POST['heslo']),0,50,"utf-8"); $heslo = password_hash($heslo, PASSWORD_DEFAULT);
$prijmeni = mb_substr(trim($_POST['prijmeni']),0,25,"utf-8"); $prijmeni = htmlspecialchars($prijmeni, ENT_QUOTES);
$jmeno = mb_substr(trim($_POST['jmeno']),0,25,"utf-8"); $jmeno = str_replace(array("'",">","<",'"'), array("","","",""), $jmeno);
$dotaz = "insert into uzivatele(ucet,heslo,prijm,jmeno) values(?,?,?,?)";
$typy = "ssss";
$parametry = array($ucet,$heslo,$prijmeni,$jmeno);
if ($stmt = $mysqli->prepare($dotaz)) {
  $stmt->bind_param($typy, ...$parametry);
  $stmt->execute();
  echo $stmt->affected_rows;
  $stmt->close();
}

UPDATE

$uid = intval($_POST['uid']);
$delka=15; $ucet = mb_substr(trim($_POST['ucet']),0,$delka,"utf-8"); $ucet=strip_tags($ucet); $ucet = filter_var($ucet, FILTER_SANITIZE_SPECIAL_CHARS);
$delka=50; $heslo = mb_substr(trim($_POST['heslo']),0,$delka,"utf-8"); $heslo = password_hash($heslo, PASSWORD_DEFAULT);
$delka=25; $prijmeni = mb_substr(trim($_POST['prijmeni']),0,$delka,"utf-8"); $prijmeni=strip_tags($prijmeni); $prijmeni = filter_var($prijmeni, FILTER_SANITIZE_SPECIAL_CHARS);
$delka=25; $jmeno = mb_substr(trim($_POST['jmeno']),0,$delka,"utf-8"); $jmeno=strip_tags($jmeno); $jmeno = str_replace(array("'",">","<",'"'), array("","","",""), $jmeno); 
$dotaz = "UPDATE uzivatele SET ucet = ?, heslo = ?, prijm = ?, jmeno = ? WHERE uid = ?";
$typy = "ssssi";
$parametry = array($ucet,$heslo,$prijmeni,$jmeno,$uid);
if ($stmt = $mysqli->prepare($dotaz)) {
  $stmt->bind_param($typy, ...$parametry);
  $stmt->execute();
  echo $stmt->affected_rows;
  $stmt->close();
}

DELETE

$uid = intval($_POST['uid']);
$dotaz = "DELETE FROM uzivatele WHERE uid = ?";
$typy = "i";
$parametry = array($uid);
if ($stmt = $mysqli->prepare($dotaz)) {
  $stmt->bind_param($typy, ...$parametry);
  $stmt->execute();
  echo $stmt->affected_rows;
  $stmt->close();
}

Získání id primárního klíče

$dotaz = "insert into uzivatele(ucet,heslo,prijm,jmeno) values(?,?,?,?)";
$typy = "ssss";
$parametry = array($ucet,$heslo,$prijmeni,$jmeno);
if ($stmt = $mysqli->prepare($dotaz)) {
  $stmt->bind_param($typy, ...$parametry);
  $stmt->execute();
  echo $mysqli->insert_id;
  $stmt->close();
}

LIKE

Toto je špatně!
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE Name LIKE %?%");

Celý trik je v tom, že v případě použití LIKE v parametrizovaném dotazu znak % zadáváme jako součást proměnné! ($prijm = "Ma%";)

$mysqli = new mysqli("localhost", "testik", "testik", "testik");
if ($mysqli->character_set_name()!="utf8") { $mysqli->set_charset("utf8"); }
// =================== parametrizovaný dotaz ===============  
$prijm = "Ma%";
$typy = "s";
$parametry = array($prijm);
$dotaz = "select * from uzivatele where prijm like ?";
//echo $dotaz."<br />";
if ($stmt = $mysqli->prepare($dotaz)) { 
  $stmt->bind_param($typy, ...$parametry);
  $stmt->execute();
  $vysledek = $stmt->get_result();
  $stmt->close();
  $poczaznamu = $vysledek->num_rows;
  while($zaznam = $vysledek->fetch_assoc()) {
    echo $zaznam['jmeno']." ".$zaznam['prijm']."<br />";
  }
}
$mysqli->close();  // není nutné

---