Instructobit
When interfacing with your database through PHP it is important to ensure the security of your data. One of the main threats is that of SQL injection, this is done by exploiting HTML inputs such as username and password inputs.
Consider the following PHP SQL statement:
$sql = 'SELECT * FROM user WHERE username="' . $username . '" AND password="' . $password . '"';
When this statement is run regularly, it will only return user information if both the username and password match a row within the user table. Let's say the user enters "john" as the username and "password123" as the password, the SQL statement will be as follows:
$sql = 'SELECT * FROM user WHERE username="john" AND password="password123"';
If a hacker instead enters a value such as " OR ""=" for both inputs then the statement will become:
$sql='SELECT * FROM user WHERE username="" OR ""="" AND password="" OR ""=""';
The above statement will return all rows within the table as ""="" always returns true.
A common approach to fend off SQL injection within PHP is to make use of prepared statements using mysqli. In this example we will be using the object oriented approach. Our user table will consist of userID, username, email and password.
$host = "localhost";
$username = "testUser";
$password = "password123";
$dbName = "testDatabase";
$con = new mysqli( $host, $username, $password, $dbName );
$sql = 'SELECT userID FROM user WHERE username=? AND password=?';
$stmnt=$con->prepare($sql);
$stmnt->bind_param( "ss", $username, $password );
$userID=NULL;
$stmnt->execute();
$stmnt->bind_result( $userID );
$stmnt->fetch();
$stmnt->close();
$con->close();
In the above example we replace any direct variable inputs into the statement with a "?" characters, this is used to signify an input into the statement, there is no need to surround this with quotations.
The SQL must then prepared, this will send the statement to the database for validation, it will return false if the statement is not valid, otherwise it will return a statement object.
The variables must then be bound to the statement, this is done using the bind_param function. This accepts 2 or more arguments: the first must be the data types of the variables in the order that they appear in the list of arguments. Data types that can be used include:
i - Integer
d - Double
s - String
b - Blob
The variables must be inserted as arguments after the list of data types in the order that they appear in the statement. If we switch around the password and username in the query, then the bind_param function will look like this:
$stmnt->bind_param( "ss", $password, $username );
After we have bound the variables to the statement we must declare variables that will be returned by the query, in this case we only have 1 variable: userID.
The query must then be executed.
After the query has executed, we need to fetch the results and store them in our userID variable. This can be done with the bind_result function which accepts one or more arguments being the results in the order they appear in the SELECT statement.
To fetch the data we run the fetch() function which will return a single row of the results, this creates another level of security to ensure not all rows can be acquired through one statements. If you want all results to be returned, the fetch function can be incorporated into a while loop, which will reset the value of userID every time fetch() is run, until there are no more results, in which case the function will return false and end the loop.
while($stmnt->fetch()){
echo($userID);
}
Lastly the statement object must be closed to ensure it can be reused further on in the script.
Protecting your database credentials using configuration files
To ensure the security of your database credentials such as the database name, username and password, they should be stored in a separate file with its own secure permissions, the permissions should be set such that users cannot view read or write from the file. You can save these credentials in a PHP file with an associative array, this can then be included in your main PHP script. It can also be reused for all of your other scripts.
config.php
<?php
$db=array("host"=>"localhost","user"=>"username","password"=>"yourPassword","dbname"=>"yourDatabaseName");
?>
test.php: mysqli connection
include("config.php");
$con = new mysqli($db["host"], $db["user"], $db["password"], $db["dbname"]);
