PDO MySQL: Utilizați PDO::ATTR_EMULATE_PREPARES sau nu? (Programare, Php, Mysql, Pdo)

Andrew Ensley a intrebat.

Aceasta este ceea ce am citit până acum despre PDO::ATTR_EMULATE_PREPARES:

  1. PDO’s prepare emulation este mai bună pentru performanță, deoarece prepararea nativă a MySQL ocolește cache-ul de interogare.
  2. Pregătirea nativă a MySQL este mai bună pentru securitate (prevenind SQL Injection).
  3. Pregătirea nativă a MySQL este mai bună pentru raportarea erorilor.

Nu știu cât de adevărată mai este oricare dintre aceste afirmații. Cea mai mare preocupare a mea în alegerea unei interfețe MySQL este prevenirea SQL Injection. A doua preocupare este performanța.

Aplicația mea utilizează în prezent MySQLi procedural (fără instrucțiuni pregătite) și utilizează destul de mult memoria cache pentru interogări. Rareori va reutiliza instrucțiuni pregătite într-o singură cerere. Am început trecerea la PDO pentru parametrii numiți și securitatea instrucțiunilor pregătite.

Folosesc MySQL 5.1.61 și PHP 5.3.2

Ar trebui să las PDO::ATTR_EMULATE_PREPARES activate sau nu? Există o modalitate de a avea atât performanța cache-ului de interogare, cât și securitatea instrucțiunilor pregătite?

Comentarii

  • Sincer? Continuați să folosiți MySQLi. Dacă funcționează deja folosind declarații pregătite în cadrul acestuia, PDO este practic un strat de abstractizare inutil. EDITARE: PDO este foarte util pentru aplicațiile green field în care nu sunteți sigur ce bază de date intră în back-end. –  > Por jmkeyes.
  • Îmi pare rău, întrebarea mea a fost neclară înainte. Am editat-o. Aplicația nu folosește declarații pregătite în MySQLi în acest moment; doar mysqli_run_query(). Din ce am citit, instrucțiunile pregătite din MySQLi ocolesc și cache-ul de interogare. –  > Por Andrew Ensley.
7 răspunsuri
Francis Avila

Pentru a răspunde la preocupările dvs:

  1. MySQL >= 5.1.17 (sau >= 5.1.21 pentru PREPARE și EXECUTE declarații) poate utiliza instrucțiuni pregătite în memoria cache pentru interogări. Așadar, versiunea dumneavoastră de MySQL+PHP poate utiliza instrucțiuni pregătite cu memoria cache pentru interogări. Cu toate acestea, țineți cont cu atenție de avertismentele privind punerea în cache a rezultatelor interogărilor din documentația MySQL. Există multe tipuri de interogări care nu pot fi stocate în memoria cache sau care sunt inutile chiar dacă sunt stocate în memoria cache. Din experiența mea, memoria cache pentru interogări nu este oricum un câștig foarte mare. Interogările și schemele au nevoie de o construcție specială pentru a utiliza la maximum memoria cache. De multe ori, pe termen lung, memoria cache la nivel de aplicație ajunge oricum să fie necesară.

  2. Pregătirile native nu fac nicio diferență în ceea ce privește securitatea. Instrucțiunile pseudo-preparate vor scăpa în continuare de valorile parametrilor de interogare, doar că acest lucru se va face în biblioteca PDO cu șiruri de caractere în loc să se facă pe serverul MySQL folosind protocolul binar. Cu alte cuvinte, același cod PDO va fi la fel de vulnerabil (sau nu) la atacurile de injecție, indiferent de EMULATE_PREPARES setare. Singura diferență este unde are loc înlocuirea parametrilor – cu EMULATE_PREPARES, , acesta are loc în biblioteca PDO; fără EMULATE_PREPARES, , are loc pe serverul MySQL.

  3. Fără EMULATE_PREPARES este posibil să apară erori de sintaxă în timpul pregătirii mai degrabă decât în timpul execuției; cu EMULATE_PREPARES veți obține erori de sintaxă doar în timpul execuției, deoarece PDO nu are o interogare pe care să o transmită către MySQL decât în timpul execuției. Rețineți că acest lucru afectează codul pe care îl veți scrie! Mai ales dacă utilizați PDO::ERRMODE_EXCEPTION!

O considerație suplimentară:

  • Există un cost fix pentru un prepare() (folosind declarații pregătite nativ), astfel încât a prepare();execute() cu instrucțiuni pregătite native poate fi puțin mai lent decât emiterea unei interogări text simplu folosind instrucțiuni pregătite emulate. Pe multe sisteme de baze de date, planul de interogare pentru a prepare() este, de asemenea, stocat în memoria cache și poate fi partajat cu mai multe conexiuni, dar nu cred că MySQL face acest lucru. Prin urmare, dacă nu reutilizați obiectul de instrucțiune pregătită pentru mai multe interogări, execuția generală poate fi mai lentă.

Ca o recomandare finală, , cred că în cazul versiunilor mai vechi de MySQL+PHP, ar trebui să emulați declarațiile pregătite, dar în cazul versiunilor foarte recente ar trebui să dezactivați emulația.

După ce am scris câteva aplicații care folosesc PDO, am realizat o funcție de conectare PDO care are ceea ce cred eu că sunt cele mai bune setări. Probabil că ar trebui să folosiți ceva de genul acesta sau să modificați setările preferate:

/**
 * Return PDO handle for a MySQL connection using supplied settings
 *
 * Tries to do the right thing with different php and mysql versions.
 *
 * @param array $settings with keys: host, port, unix_socket, dbname, charset, user, pass. Some may be omitted or NULL.
 * @return PDO
 * @author Francis Avila
 */
function connect_PDO($settings)
{
    $emulate_prepares_below_version = '5.1.17';

    $dsndefaults = array_fill_keys(array('host', 'port', 'unix_socket', 'dbname', 'charset'), null);
    $dsnarr = array_intersect_key($settings, $dsndefaults);
    $dsnarr += $dsndefaults;

    // connection options I like
    $options = array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    );

    // connection charset handling for old php versions
    if ($dsnarr['charset'] and version_compare(PHP_VERSION, '5.3.6', '<')) {
        $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES '.$dsnarr['charset'];
    }
    $dsnpairs = array();
    foreach ($dsnarr as $k => $v) {
        if ($v===null) continue;
        $dsnpairs[] = "{$k}={$v}";
    }

    $dsn = 'mysql:'.implode(';', $dsnpairs);
    $dbh = new PDO($dsn, $settings['user'], $settings['pass'], $options);

    // Set prepared statement emulation depending on server version
    $serverversion = $dbh->getAttribute(PDO::ATTR_SERVER_VERSION);
    $emulate_prepares = (version_compare($serverversion, $emulate_prepares_below_version, '<'));
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, $emulate_prepares);

    return $dbh;
}

Comentarii

    27

  • Re #2: cu siguranță valorile pe care MySQL le primește ca parametri (pentru declarațiile pregătite native) nu sunt analizate pentru SQL deloc? Deci, riscul de injectare trebuie să fie fi mai mic decât dacă se folosește emulația de preparare a PDO, unde orice defect de scăpare (de exemplu, problemele istorice mysql_real_escape_string cu caracterele multibyte) ar lăsa în continuare o persoană expusă atacurilor de injecție? –  > Por eggyal.
  • @eggyal, faceți presupuneri cu privire la modul în care sunt implementate declarațiile pregătite. Este posibil ca PDO să aibă o eroare în modul de scăpare a preparatelor emulate, dar și MySQL ar putea avea erori. AFAIK, nu au fost descoperite probleme cu emulate prepares care ar putea face ca parametrii literali să treacă prin unescaped. –  > Por Francis Avila.
  • Minunat răspuns, dar am o întrebare: Dacă dezactivați EMULAȚIA, execuția nu va fi mai lentă? PHP ar trebui să trimită instrucțiunea pregătită către MySQL pentru validare și abia apoi să trimită parametrii. Deci, dacă folosiți instrucțiunea pregătită de 5 ori, PHP va vorbi cu MySQL de 6 ori (în loc de 5). Acest lucru nu va face ca execuția să fie mai lentă? În plus, cred că există o șansă mai mare ca PDO să aibă erori în procesul de validare, decât MySQL… –  > Por Radu Murzea.
  • Rețineți aspectele prezentate în acest răspuns cu privire la emulația declarațiilor pregătite folosind mysql_real_escape_string sub capotă și vulnerabilitățile care pot apărea în consecință (în cazuri limită foarte particulare). –  > Por eggyal.
  • +1 Răspuns bun! Dar, ca să se știe, dacă folosiți prepararea nativă, parametrii nu sunt niciodată evadați sau combinați în interogarea SQL, nici măcar pe partea de server MySQL. În momentul în care executați și furnizați parametrii, interogarea a fost analizată și transformată în structuri de date interne în MySQL. Citiți acest blog al unui inginer de optimizare MySQL care explică acest proces: guilhembichot.blogspot.com/2014/05/… Nu spun că acest lucru înseamnă că pregătirea nativă este mai bună, în măsura în care avem încredere în codul PDO pentru a face escaping corect (ceea ce eu fac). –  > Por Bill Karwin.
dallin

Sunt surprins că nimeni nu a menționat unul dintre cele mai mari motive pentru a dezactiva emulația. Cu emulația activată, PDO returnează toate numerele întregi și flotante ca șiruri de caractere. Când dezactivați emulația, numerele întregi și flotante din MySQL devin numere întregi și flotante în PHP.

Pentru mai multe informații, consultați răspunsul acceptat la această întrebare: PHP + PDO + MySQL: cum returnez coloanele întregi și numerice din MySQL ca numere întregi și numerice în PHP?

Sage Pointer

Atenție la dezactivarea PDO::ATTR_EMULATE_PREPARES (activarea pregătirilor native) atunci când PHP pdo_mysql nu este compilat în funcție de mysqlnd.

Deoarece vechile libmysql nu este pe deplin compatibil cu anumite funcții, poate duce la apariția unor erori ciudate, de exemplu:

  1. Pierderea celor mai semnificativi biți pentru numere întregi pe 64 de biți atunci când se face legătura ca PDO::PARAM_INT ( 0x12345678AB va fi tăiat la 0x345678AB pe o mașină pe 64bit )
  2. Imposibilitatea de a efectua interogări simple precum LOCK TABLES ( se aruncă SQLSTATE[HY000]: General error: 2030 This command is not supported in the prepared statement protocol yet excepție )
  3. Necesitatea de a prelua toate rândurile din rezultat sau de a închide cursorul înainte de următoarea interogare ( cu mysqlnd sau pregătiri emulate, acesta face automat această muncă pentru dvs. și nu se desincronizează cu serverul mysql )

Aceste erori mi-am dat seama în proiectul meu simplu când am migrat pe alt server care folosea libmysql pentru pdo_mysql modulul. Poate sunt mult mai multe bug-uri, nu știu. De asemenea, am testat pe debian jessie proaspăt 64bit debian jessie, toate bug-urile enumerate apar atunci când eu apt-get install php5-mysql, , și dispar atunci când apt-get install php5-mysqlnd.

Când PDO::ATTR_EMULATE_PREPARES este setat la true (ca implicit) – aceste erori nu se întâmplă oricum, deoarece PDO nu folosește deloc declarații pregătite în acest mod. Deci, dacă utilizați pdo_mysql bazat pe libmysql („mysqlnd” substring nu apare în câmpul „Client API version” din pdo_mysql secțiunea din phpinfo) – nu ar trebui să transformați PDO::ATTR_EMULATE_PREPARES off.

Comentarii

  • este această preocupare încă valabilă în 2019?!!!  > Por oldboy.
Will Morgan

Aș dezactiva emulate prepares, deoarece executați 5.1, ceea ce înseamnă că PDO va profita de funcționalitatea nativă a declarațiilor pregătite.

PDO_MYSQL va profita de suportul nativ al declarațiilor pregătite prezent în MySQL 4.1 și versiunile ulterioare. Dacă utilizați o versiune mai veche a bibliotecilor client mysql, PDO le va emula pentru dumneavoastră.

http://php.net/manual/en/ref.pdo-mysql.php

Am renunțat la MySQLi pentru PDO pentru declarațiile numite pregătite și pentru API-ul mai bun.

Cu toate acestea, pentru a fi echilibrat, PDO se comportă neglijabil mai lent decât MySQLi, dar este un lucru de care trebuie să țineți cont. Știam acest lucru când am făcut alegerea și am decis că o API mai bună și folosirea standardului industriei era mai importantă decât folosirea unei biblioteci neglijabil mai rapide care te leagă de un anumit motor. FWIW cred că echipa PHP privește, de asemenea, cu ochi buni PDO în locul MySQLi și pentru viitor.

Comentarii

  • Vă mulțumim pentru această informație. Cum a influențat performanța dvs. faptul că nu ați putut utiliza memoria cache pentru interogări sau chiar o foloseați înainte? –  > Por Andrew Ensley.
  • Nu pot să spun, deoarece cadrul pe care îl folosesc eu folosește oricum memoria cache pe mai multe niveluri. Puteți folosi întotdeauna în mod explicit SELECT SQL_CACHE <rest of statement> totuși. –  > Por Will Morgan.
  • Nici măcar nu știam că există o opțiune SELECT SQL_CACHE. Cu toate acestea, se pare că tot nu ar funcționa. Din documentație: „Rezultatul interogării este pus în cache dacă poate fi pus în cache…” dev.mysql.com/doc/refman/5.1/en/query-cache-in-select.html –  > Por Andrew Ensley.
  • Da. Aceasta depinde de natura interogării, mai degrabă decât de specificul platformei. –  > Por Will Morgan.
  • Am citit asta ca însemnând „Rezultatul interogării este pus în cache”. cu excepția cazului în care altceva îl împiedică să fie pus în cache,” ceea ce – din ceea ce citisem până atunci – includea declarațiile pregătite. Cu toate acestea, datorită răspunsului lui Francis Avila, știu că acest lucru nu mai este valabil pentru versiunea mea de MySQL. –  > Por Andrew Ensley.
quickshiftin

V-aș recomanda să activați baza de date reală PREPARE deoarece emulația nu prinde totul…, de exemplu, va pregăti INSERT;!

var_dump($dbh->prepare('INSERT;'));
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
var_dump($dbh->prepare('INSERT;'));

ieșirea

object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(7) "INSERT;"
}
bool(false)

Sunt dispus să accept cu plăcere o scădere a performanței pentru un cod care chiar funcționează.

FWIW

Versiunea PHP: PHP 5.4.9-4ubuntu2.4 (cli)

Versiunea MySQL: 5.5.34-0ubuntu0

Comentarii

  • Este un punct de vedere interesant. Presupun că emulația amână parsarea pe partea de server pentru faza de execuție. Deși nu este o mare problemă (SQL greșit va eșua în cele din urmă), este mai curat să lași prepare să-și facă treaba pe care trebuie să o facă. (În plus, am presupus întotdeauna că analizatorul de parametri de pe partea clientului va avea în mod necesar propriile erori). –  > Por Álvaro González.
  • Nu știu dacă vă interesează, dar iată o mică prezentare despre alte comportamente false pe care le-am observat cu PDO și care m-au condus în această gaură de iepure. Se pare că gestionarea interogărilor multiple lipsește. –  > Por quickshiftin.
  • Tocmai m-am uitat la niște biblioteci de migrare de pe GitHub… Ce știi tu.., aceasta face exact același lucru ca și postul meu de pe blog. –  > Por quickshiftin.
Harry Bosh

De ce să comutați emulația la „fals”?

Motivul principal este că, dacă motorul bazei de date face pregătirea în loc de PDO, interogarea și datele reale sunt trimise separat, ceea ce sporește securitatea. Acest lucru înseamnă că atunci când parametrii sunt trecuți la interogare, încercările de a injecta SQL în ei sunt blocate, deoarece declarațiile pregătite de MySQL sunt limitate la o singură interogare. Aceasta înseamnă că o instrucțiune pregătită adevărată ar eșua atunci când i se trece o a doua interogare într-un parametru.

Argumentul principal împotriva utilizării motorului bazei de date pentru prepararea vsPDO este faptul că trebuie să se facă două drumuri către server – unul pentru prepararea și altul pentru transmiterea parametrilor – dar cred că securitatea suplimentară merită. De asemenea, cel puțin în cazul MySQL, memoria cache pentru interogări nu mai este o problemă de la versiunea 5.1.

https://tech.michaelseiler.net/2016/07/04/dont-emulate-prepared-statements-pdo-mysql/

Comentarii

  • Query caching a dispărut oricum: Memoria cache pentru interogări este depreciată începând cu MySQL 5.7.20 și este eliminată în MySQL 8.0. –  > Por Álvaro González.
magallanes

Pentru înregistrare

PDO::ATTR_EMULATE_PREPARES=true

Ar putea genera un efect secundar neplăcut. Ar putea returna valori int ca șir de caractere.

PHP 7.4, pdo cu mysqlnd.

Rularea unei interogări cu PDO::ATTR_EMULATE_PREPARES=true

Coloană : id
Tip :integer
Valoare : 1

Rularea unei interogări cu PDO::ATTR_EMULATE_PREPARES=false

Coloană : id
Tip :string
Valoare : „1”

În orice caz, valorile zecimale sunt returnate întotdeauna un șir de caractere, indiferent de configurație 🙁

Comentarii

  • valorile zecimale sunt întotdeauna returnate sub forma unui șir de caractere este singurul mod corect – -.  > Por Bunul dumneavoastră simț.
  • Da, din punctul de vedere al MySQL, dar este greșit din partea PHP. Atât Java, cât și C# consideră zecimalele ca fiind o valoare numerică. –  > Por magallanes.
  • Nu, nu este așa. Este ca toate corecte pentru întreaga informatică. Dacă tu crezi că este greșit, atunci ai nevoie de un alt tip, de precizie arbitrară –  > Por Bunul tău simț..
  • @YourCommonSense Vrei să sapi puțin mai adânc în motivul pentru care crezi asta? –  > Por Robert McKee.
  • @YourCommonSense un float nu poate reprezenta cu acuratețe 0,2, în timp ce o zecimală poate. Dar, aceasta este o problemă de PHP, nu o problemă de informatică întreagă. Multe limbaje (și baze de date) au tipuri de date intrinseci care pot reprezenta cu acuratețe numere precum 0,2. PHP poate, dar nu există un tip de date intrinsec în limbajul de bază. Dar să spui că valorile zecimale sunt întotdeauna returnate sub forma unui șir de caractere este singurul mod corect este meh. Asta presupunând că ați prefera ca 0,2 să fie reprezentat cu acuratețe în loc ca „12” să fie sortat înaintea lui „2”. De asemenea, „2” nu este egal cu „2,0”. –  > Por Robert McKee.

Tags:, ,