Lesson 6.5. SQL queries in PHP

Foreword

We'll assume the existence of the variable $db. This variable contains the [database connexion parameters(/en/web-dev-class/lesson-6-04-mysql-connection-in-php/#connection) created according to the source code presented on the page Connexion MySQL en PHP.

Syntax

The syntax for submitting a MySQL query is quite simple. It creates a string containing the SQL query and call the the method exec() from the PDO class (object $db) :

$sql = "INSERT INTO `users` (`firstname`, `lastname`) VALUES ('Lary', 'Page')";
$db->exec($sql);

Unfortunately, this syntax does not protect against SQL injections. If the values in the query (here Larry and Page) comes from a form, they will be inserted directly into the string.

Parametric queries

To prevent SQL injections, you should separate the values of the SQL commands. Here is the previous example with the values extracted from the query :

$query = $db->prepare( "INSERT INTO `users` (`firstname`, `lastname`) VALUES (:firstname, :lastname)");
$nom ='Lary';
$query->bindParam(':firstname', $nom);
$query->bindValue(':lastname', 'Page');
$query->execute();

You will notice that the query no longer contains the values, but labels (or parameters) (:firstname and :lastname) which will be associated with their values when the bindParam or bindValue function is called. These parameters must start with a colon so that the MySQL engine can MySQL engine can differentiate them from commands or other values.

bindParam allows to bind a label to a variable
bindValue allows to bind a label to a constant (typically a string)

We strongly recommand usin parametric queries for security reasons.

Exercise

Using phpMyAdmin, create a new database named myBase.

Run the following SQL command in phpMyAdmin to create a new table:

CREATE TABLE users (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  lastname VARCHAR(100),
  firstname VARCHAR(100)
);

After writing the [database connection code]((/en/web-dev-class/lesson-6-04-mysql-connection-in-php/#connection), add the following lines:

$prenom = 'Lary';
$nom = 'page';

$sql = "INSERT INTO `users` (`firstname`, `lastname`) VALUES ( '".$prenom."', '".$nom."' )";
$db->exec($sql);

Check in phpMyAdmin that a new row has been created in the users table.

Test again with the following code:

$prenom = 'Lary';
$nom = "Page'); DROP TABLE users; -- ";

$sql = "INSERT INTO `users` (`firstname`, `lastname`) VALUES ( '$prenom', '$nom' )";
$db->exec($sql);

Check in phpMyAdmin that a new row has been created in the users table.

Repeat the exercise with parametric queries (prepare, bindParam and execute).

Lesson 6.4 Summary Lesson 6.6

Lesson 6.5. SQL queries in PHP

Foreword

Syntax

Parametric queries

Exercise

See also