25 05 2014

Il y a de cela un petit moment, j’avais lu une discussion à propos des requêtes préparées. (prepared statements en anglais)

C’est quoi une requêtes préparée?

La vraie question serait : qu’est-ce qui la différencie d’une requête normale?

Une requête normale :
[cc lang= »sql »]
select mot_de_passe from user where login= ‘Portekoi’
[/cc]

Une requête préparée :

[cc lang= »sql »]
select mot_de_passe from user where login= ?
[/cc]

La différence réside dans la méthode de passation des valeurs. Par exemple, ici, le terme « Portekoi » est placé directement dans la requête dans l’exemple 1.

Dans l’exemple 2, la valeur est transmise séparément. C’est l’un des avantages de cette méthode.

Pourquoi utiliser des requêtes préparées?

Il y a en réalité deux avantages :

La sécurité

Comme vu précédemment, la 1ière méthode implique de placer les variables, bien souvent saisies par un utilisateur, dans la requête directement.
Si je reprends la première requête en injectant un code malveillant :
[cc lang= »sql »]
select mot_de_passe from user where login =  »; delete * from user where 1 = ‘1’
[/cc]
Le code injecté est celui-ci :

‘; delete * from user where 1 = ‘1

Si le code n’est pas protégé, cette requête supprimera toutes les données de la table ‘user’

Dans une requête préparée, lorsque l’on transmet les variables, on transmet aussi le type de cette donnée.

Exemple : (Tiré du site : http://www.lephpfacile.com/manuel-php/mysqli.prepare.php )
[cc lang= »php »]
$mysqli = new mysqli(« localhost », « root », « mdp », « basedetest »);

$login= « Portekoi »;

/* Crée une requête préparée */
if ($stmt = $mysqli->prepare(« SELECT mot_de_passe FROM user WHERE login = ? and type = ? »)) {

/* Le premier paramètre est le type de la variable. Les ‘n’ suivants sont les variables. */
/* J’ai volontairement ajouté une seconde close dans le where que je transmets en dur : */
/* %s : string / %d : décimal / %i : int / %b : blob */
$stmt->bind_param(« %s%s », $login, ‘root’);

/* Exécution de la requête */
$stmt->execute();
}
[/cc]

Dans une requête préparée, les données et la requête sont envoyées séparément au serveur. Ainsi, on ne peut l’altérer.

Il n’y a plus besoin de gérer les « quotes » ou la fonction « mysqli_real_escape_string() »

Les performances

Voici les échanges avec le serveur lors d’une requête normale :

• Envoi de la requête au serveur
• Compilation
• Création du plan d’exécution
• Exécution de la requête
• Envoi de la réponse

Si la même requête est envoyée 2 fois, ce schéma sera reproduit.

Maintenant, avec une requête préparée :

• Envoi de la requête au serveur
• Compilation
• Création du plan d’exécution
• Stockage de la requête
• Envoi de la réponse avec un identifiant

En cas de requêtes répétées :

• Envoi de la même requête au serveur
• Exécution
• Envoi de la réponse

L’utilisation

Comme vu juste avant, l’utilisation des requêtes préparées est un peu lourd à mettre en place.
En effet, entre la requête, la préparation, l’association des variables et l’exécution, cela peut paraitre un peu difficile à utiliser.

Voici un exemple de requête en utilisant une classe trouvée sur le web. J’ai ajouté un pattern « décorateur » afin de renvoyer : un état, un nombre de ligne et le résultat.
[cc lang= »php »]
//Connexion
$db = new DBExtend($login_db, $mdp_db, $base_db);

$result = $db->DoSelect(‘SELECT mot_de_passe FROM user WHERE login = ? and type = ?’, array($login, ‘root’), array(‘%s’, ‘%s’));

//Lecture des résultats :
if ($result[« num_rows »] > 0) {
//Lecture
foreach($result[« rows »] as $key => $val){

}
}
//débogue
var_dump($result);

//Un insert : traduction : insert into ma_tabme (champs1, champs2) values (‘valeur1’, ‘valuer2’)
$result = $db->DoInsert(« ma_table », array(« champ1 » => « valeur1 », « champ2_public » => « valeur2 »), array(« %s », « %s »));

//Un update : traduction : update ma_table set champ1 = ‘valeur1’, champ2 = ‘valeur2’ where id = 68
$result = $db->DoUpdate(« ma_table », array(« champ1 » => « valeur1 », « champ2 » => « valeur2 »), array(« %s », « %s ») , array(« id » => 68), array(« %d »));
[/cc]

On voit ici clairement la force des requêtes préparées. Meilleure sécurité et performances accrues. Je place ci-dessous les classes PHP.

Class « DB » / Source : http://www.johnmorrisonline.com/simple-php-class-prepared-statements-mysqli/
[cc lang= »php »]
user = $user;
$this->password = $password;
$this->database = $database;
$this->host = $host;
}
protected function connect() {
$c = new mysqli($this->host, $this->user, $this->password, $this->database);
$c->set_charset(« utf8″);
return $c;
}
protected function query($query) {
$db = $this->connect();
$result = $db->query($query);
while ( $row = $result->fetch_object() ) {
$results[] = $row;
}
return $results;
}

protected function insert($table, $data, $format) {
// Check for $table or $data not set
if ( empty( $table ) || empty( $data ) ) {
return false;
}
// Connect to the database
$db = $this->connect();
// Cast $data and $format to arrays
$data = (array) $data;
$format = (array) $format;
// Build format string
$format = implode( », $format);
$format = str_replace(‘%’,  », $format);
list( $fields, $placeholders, $values ) = $this->prep_query($data);
// Prepend $format onto $values
array_unshift($values, $format);

// Prepary our query for binding
$stmt = $db->prepare(« INSERT INTO {$table} ({$fields}) VALUES ({$placeholders}) »);

// Dynamically bind values
call_user_func_array( array( $stmt, ‘bind_param’), $this->ref_values($values));
// Execute the query
$stmt->execute();
//close
$db->close();
// Check for successful insertion
Tools::WriteFile(« logs.txt », « ##Statement### »);
Tools::WriteFile(« logs.txt », $stmt->affected_rows);
Tools::WriteFile(« logs.txt », « ##Statement### »);
if ( $stmt->affected_rows >= 0) {
return $stmt->insert_id;
exit();
}

return $stmt->affected_rows;
}

protected function update($table, $data, $format, $where, $where_format) {
// Check for $table or $data not set
if ( empty( $table ) || empty( $data ) ) {
return false;
}
// Connect to the database
$db = $this->connect();
// Cast $data and $format to arrays
$data = (array) $data;
$format = (array) $format;
// Build format array
$format = implode( », $format);
$format = str_replace(‘%’,  », $format);
$where_format = implode( », $where_format);
$where_format = str_replace(‘%’,  », $where_format);
$format .= $where_format;
list( $fields, $placeholders, $values ) = $this->prep_query($data, ‘update’);
//Format where clause
$where_clause =  »;
$where_values =  »;
$count = 0;
foreach ( $where as $field => $value ) {
if ( $count > 0 ) {
$where_clause .= ‘ AND ‘;
}
$where_clause .= $field . ‘=?’;
$where_values[] = $value;
$count++;
}

// Prepend $format onto $values
array_unshift($values, $format);
$values = array_merge($values, $where_values);

// Prepary our query for binding
$stmt = $db->prepare(« UPDATE {$table} SET {$placeholders} WHERE {$where_clause} »);
// Dynamically bind values
call_user_func_array( array( $stmt, ‘bind_param’), $this->ref_values($values));
// Execute the query
$stmt->execute();
//close
$db->close();
// Check for successful insertion
if ( $stmt->affected_rows ) {
return $stmt->affected_rows;
}
return $stmt->affected_rows;
}
protected function select($query, $data, $format) {
// Connect to the database
$db = $this->connect();
//Prepare our query for binding
$stmt = $db->prepare($query);
//Normalize format
$format = implode( », $format);
$format = str_replace(‘%’,  », $format);
// Prepend $format onto $values
array_unshift($data, $format);
//Dynamically bind values
call_user_func_array( array( $stmt, ‘bind_param’), $this->ref_values($data));
//Execute the query
$stmt->execute();
//Fetch results
$result = $stmt->get_result();
//close
$db->close();
//Create results object
//fetch_assoc pour obtenir un tableau
while ($row = $result->fetch_assoc()) {
$results[] = $row;
}

return $results;
}
protected function delete($table, $id) {
// Connect to the database
$db = $this->connect();
// Prepary our query for binding
$stmt = $db->prepare(« DELETE FROM {$table} WHERE ID = ? »);
// Dynamically bind values
$stmt->bind_param(‘d’, $id);
// Execute the query
$stmt->execute();
// Check for successful insertion
if ( $stmt->affected_rows ) {
return true;
}
}
private function prep_query($data, $type=’insert’) {
// Instantiate $fields and $placeholders for looping
$fields =  »;
$placeholders =  »;
$values = array();
// Loop through $data and build $fields, $placeholders, and $values
foreach ( $data as $field => $value ) {
$fields .= « {$field}, »;
$values[] = $value;
if ( $type == ‘update’) {
$placeholders .= $field . ‘=?,’;
} else {
$placeholders .= ‘?,’;
}
}
// Normalize $fields and $placeholders for inserting
$fields = substr($fields, 0, -1);
$placeholders = substr($placeholders, 0, -1);
return array( $fields, $placeholders, $values );
}
private function ref_values($array) {
$refs = array();

foreach ($array as $key => $value) {
$refs[$key] = &$array[$key];
}

return $refs;
}
}
}
?>
[/cc]

Ma classe « décorateur » :
[cc = lang= »php »]
InitialiseResult();
$t = $this->insert($table, $data, $format);

if($t >= 0){
$r[« state »] = true;
$r[« last_id »] = $t;
}

return $r;
}

public function DoSelect($query, $data, $format){

$r = $this->InitialiseResult();

$t = $this->select($query, $data, $format);

if(count($t) >= 0){
$r[« state »] = true;
$r[« rows »] = $t;
$r[« num_rows »] = count($t);
}

return $r;
}

public function DoUpdate($table, $data, $format, $where, $where_format){

$r = $this->InitialiseResult();
$t = $this->update($table, $data, $format, $where, $where_format);
if($t >= 0){
$r[« state »] = true;
$r[« num_rows »] = $t;
}

return $r;
}

/**
* mysqli::InitialiseResult()
*
* @param mixed $p
* @return
*/
private function InitialiseResult()
{
$r = array(); //on écrase
$r[« state »] = false;

switch($p){
case « select »:

$r[« rows »] = array();
$r[« num_rows »] = 0;
break;

}

return $r;
}

}
?>
[/cc]

Sauvegarde d’une base Mysql avec ou sans les données [SQL Server] : Retourner une valeur depuis un EXEC via sp_executesql