Parameterised SQL

Parameterised SQL should be used whenever you are querying a SQL database using external input (say a GET or POST value) to avoid SQL injection attacks.

parameterisation.png

Parameterised SQL using object-oriented MySQLi

When working with any sort of SQL thing, you first need to connect to a database, because... otherwise there would be no way to pull any data from the database. This can be achieved using a single line of code.

$sqlServer = "localhost";
$sqlUser = "user";
$sqlPassword = "password";
$sqlDatabase = "bestDatabase";

$conn = mysqli_connect($sqlServer, $sqlUser, $sqlPassword, $sqlDatabase); // Create connection to SQL

You should then check that your connection actually worked. If something didn't, mysqli_connect will return false, which can be used in an if statement.

if (!$conn) { // if that connection didn't work
    die("Connection failed. " . mysqli_connect_error());
}

Once a connction has been established, we can begin to execute SQL. Because we're going to use parameterised statements, we use a ? symbol to represent areas where values will be placed. In this example, we will be selecting every column in the table users where the country is provided as a POST variable.

$sql = $conn->prepare("SELECT * FROM users WHERE country = ?");
$sql->bind_param("s", $_POST["country"]);

The first argument of bind_param specifies what data type should be expected in the variable provided as the second argument. This can be one of four characters:

  • i - integer
  • d - double
  • s - string
  • b - BLOB

Any external data provided in the second argument must be sanitized and validated.

Now we can execute our SQL, and store the result.

$sql->execute();
$results = $sql->get_result();

get_result returns a mysqli_result object, which is best viewed as a table. Every row in the database that is returned is represented by a row in the table. You can then iterate over every row in this table to deal with all data provided.

if ($results->num_rows > 0) {
	while($row = $results->fetch_assoc()) {
		echo "id: " . $row["id"] . " name: " . $row["name"] . "<br>";
	}
} else {
	echo "Nothing was returned :(";
}

We first check to see if any rows were actually returned - if not we don't want to try and iterate over anything, and instead display a message that says no results were returned.

Then we move on to iterating over the results. For this we use fetch_assoc. When fetch_assoc is called, it will either return an array with elements, or null. If null is returned, the loop will exit, else the loop continues. Values can be referenced inside the loop referencing $row, with the items in row being column names in the original database, or the columns that were specified in the query.

If you want, at this point you can change the value of the variable referenced in bind_param, run execute again, and go through the data for a second time without having to redefine the same SQL.

When you're done with the query, you should close it to allow the connection to be used for other queries later on.

$sql->close();

Complete code:

<?php
$sqlServer = "localhost";
$sqlUser = "user";
$sqlPassword = "password";
$sqlDatabase = "bestDatabase";

$conn = mysqli_connect($sqlServer, $sqlUser, $sqlPassword, $sqlDatabase); // Create connection to SQL

if (!$conn) { // if that connection didn't work
    die("Connection failed. " . mysqli_connect_error());
}

$sql = $conn->prepare("SELECT * FROM users WHERE country = ?");

$sql->bind_param("s", $_POST["country"]);
$sql->execute();
$results = $sql->get_result();

if ($results->num_rows > 0) {
	while($row = $results->fetch_assoc()) {
		echo "id: " . $row["id"] . " name: " . $row["name"] . "<br>";
	}
} else {
	echo "Nothing was returned :(";
}

$sql->close();
?>