PHP Security Courses SQL Injection (Part 3)

While XSS represents an indirect attack on users, SQL Injection represents a direct attack on a PHP application or a database. The purpose of this attack is to change a specific SQL query to complete various actions, from getting alternate data, to modifying or deleting data from the database.

SQL Injection attacks are mainly performed on authentication scripts when checking user names and passwords so that the SQL query is modified and always executed so that it always “retrieves” a user’s information.

The following example demonstrates a bug when checking user data:

// login form data
$username = $_POST['username'];
$password = $_POST['password'];
// data verification
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysql_query($sql);
if(mysql_num_rows($result) > 0) {
    // user logged in ...

If the attacker in this login form instead of the username and password entered the following value:

a' OR '1' = '1

The SQL query that would run would look like this:

SELECT * FROM users WHERE username = 'a' OR '1'='1' AND password = 'a' OR '1'='1'

Such a query has certainly returned the information to the user, and in this particular case, the user could be logged. By adding some other conditions. For example, WHERE id = ‘1’ or some other ID, there is a high probability that it will be authorized as a system administrator.

Except for authentication testing, it can also be used for other malicious purposes, and one of them is adding an additional query that may destroy data or other malicious actions.

The following example demonstrates SQL injection to perform an additional query:

// ubacivanje dodatnog upita
$name = "Alen'; DELETE FROM users”;
mysql_query("SELECT * FROM users WHERE name='$name'");

Multiple queries are not supported at the same time by the mysql_query () function, but other drivers, such as PostgreSQL or SQLite, allow the execution of such a code as a serious security vulnerability.


As already mentioned in previous posts, input filtering can prevent most security issues and is a mandatory secure application item. If we filter the data by banning the user from entering special characters specific to SQL queries, for example, apostrophes, and for passwords, we can use single-encryption, for example, md5, would certainly increase security.

However, there are other methods to prevent the attack, and to allow all characters:

• Escape Input – mysql_real_escape_string ()

• Prepared phrases

mysql_real_escape_string ()

This function belongs to a MySQL driver, but it also has a mysqli driver (mysqli_real_escape_string) and ensures that all special characters are properly executed, that is, when constructing the query, they continue to behave as an integral part of that string and never use it in the query change.

This function is “best practice” and must be executed overall changes that make the query.

$username = mysql_real_escape_string($_POST['username']);

The query for the above example after the escape variable looks like this:

SELECT * FROM users WHERE username = 'a\' OR \'1\'=\'1' AND password = 'a\' OR \'1\'=\'1'

These embedded functions belong to the database driver, all the lines “\” will not be entered with other data in the database, which is not the case with the addslashes function.

Note: It is assumed that magic_quotes_gpc is turned off, otherwise, the data must be normalized.

Prepared Statements

Prepared statements are not such big news, but many beginners do not want to use them, and probably because of their poor understanding of how they work. First of all, it should be noted that the prepared terms cannot be used in older versions of the driver, such as standard mysql. The Mysql driver is an older date and its further development has long been suspended. Even the recommendation of the PHP consortium is that it is completely dropped from use and that drivers such as MySQLi and PDO are used. These two drivers are much faster and have a lot of security features.

The expression preparation or its construction takes place on MySQL server, so there is no possibility of changing the query either in the PHP application or on the server. When writing queries, instead of changing values, use “?” operator. Changes are sent with a special function in the order in which they are entered in the query.

The following code describes the work of MySQLi drivers and prepared statements:

$username = $_POST['username'];
$password = $_POST['password'];
// Connection with MySQL database
$mysqli = new mysqli('localhost', 'user', 'password', 'world');
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ? AND password = ? LIMIT 1");
// The values of the variable are sent to the query
$stmt->bind_param('ss', $username, $password);
// executing queries
if($stmt->affected_rows > 0) {
    // user is logged
// closing expressions and queries

Learn more about MySQL and its functionality at:

4 0

Leave a Reply

Your email address will not be published. Required fields are marked *