Tips for Stopping Website Hacks & Hacking: Preventing SQL Injections

What is an SQL injection?

Hackers can target a website by adding SQL (Structured Query Language) into an application to make changes to the database. For example, an SQL statement that selects fields from a table could be manipulated by adding ‘DROP TABLE’ after the value. This would then delete the entire table. 

SQL injections can be used to view personal information such as passwords, delete entire tables and gaining access to the database by use of admin credentials.  

How to prevent a SQL injection?

Fortunately, an SQL Injection can be prevented by a ‘prepare’ statement. A prepare statement will send the program to the sever first and it will use a placeholder such as ‘?’ in its place.  

For example: $db->prepare(“SELECT * FROM  media where id=?”);  

The data of the placeholder gets sent separately to the query so the any user input is not treated as a SQL statement and therefore is not executed.  

When should you use a prepare statement?

Generally, any form of data that is touched by a user or that pulls data from a database, should use a prepared statement.  

This can include:  

• Usernames 
• Email addresses 
• Passwords 
• Search queries 
• File uploading 

Using a prepare statement

We use a prepare keyword and the placeholder ‘?’ is used as a placeholder where the value will go. 

We then bind the values to the query using ‘bind_param’. This is also attached with the correct data type so in this case it is a string – hence “s”.  

The query is then executed – $stmt->execute(); 

The bind_result() is used to bind the results and then this can be displayed by looping through the results.  

In this example, the user fills out a search field with a city name. This value is stored in the variable $term. This selects all the images that matches the user’s entered location.  

<?php  

$term = “$_POST[‘city‘]”; 

$stmt = $conn->prepare(“SELECT image_name FROM media WHERE location LIKE ?“); 

$stmt->bind_param(“s”, $term); 

$stmt->execute(); 

$stmt->bind_result($image_name); 

?> 

Displaying the results

while($stmt->fetch()) 

{ 

    echo $image_name; 

} 

Conclusion

SQL injections are often overlooked and can be a common threat, therefore suitable prevention is essential in keeping your database and it’s data safe. Separating the data and the query will ensure that your program cannot be manipulated.