SQL injections are one of the most popular hacking techniques. It involves injecting a SQL code for a malicious intent, such as to destroy a database or to get private information. For instance, a hacker can inject SQL code into a website form to retrieve a list of usernames and passwords from the database. This attack can threaten your users’ sensitive personal information- imagine all the data the hackers can get! That is one of the simplest forms of SQL injection: user input.
Fortunately, SQL injections are not difficult to prevent. This hacking technique is not sophisticated and it can be rather easily avoided implementing different code solutions. It’s just one more security measure that can be easily forgotten.
Even the popular game Fortnite had a vulnerability that could have caused a SQL injection attack that would have allowed hackers to take over game accounts. Tim Mackey, technical evangelist at Synopsys, stated the following: “SQL Injection and XSS are perennial items on the OWASP Top 10”.
Is your code SQL injection-proof? Did you suffer from a SQL injection security attack? Keep on reading to find the solution!
Top 3 Ways to Protect Your Database Against MySQL Injections
1. Escape characters using MySQLi
This method allows the database to see the SQL injection as text, not code. This will avoid any malicious action intended by the hacker’s code. The idea is to make the database escape the text and convert it into characters. The following example is for a simple website form. This solution was provided by Multimedia Tutorials YouTube channel:
Start by setting variables equal to the actual data. See example below for the first name field:
$first = $_POST['first'];
Then, insert the following PHP function:
Inside that parenthesis you’ll add two different parameters: the database connection to the database and the data the user passes on in the form. The code will look similar to this:
$first = mysqli_real_escape_string ($conn, $_POST['first']);
Where $conn is your database connection to the database. Then, follow the same steps with the rest of the fields. You can easily copy and paste mysqli_real_escape_string ($conn), to accelerate the process.
2- Use a Web Application Firewall
A Web Application Firewall (WAF) works as an added layer of security by detecting suspicious activity coming from your website traffic. The illegitimate traffic is filtered out based on a specific set of rules. Technically, it examines the HTTPS requests coming in to your website.
Since it sits at the DNS level, if it detects any malicious activity, it won’t let it go through to the database.
In the event the Web Application Firewall detects suspicious behavior, you can choose how to proceed. For instance, you can ask the user to submit a CAPCHA, or automatically block a suspicious IP address. Implementing a Web Application Firewall will also help you prevent Cross-Site Scripting (XSS), and Cross-Site Forgery (CSRF). It may sound like the perfect solution, but keep in mind that is not a 100% fail-proof method. There are still ways someone can bypass the WAF. Even then, I’d strongly recommend it.
3. Using Prepared Statements to Protect Database Against MySQL Injections
This method uses placeholders instead of actual variables, which are sent to the database, get parsed, and eventually get replaced inside the SQL statements. Note that the database will also not see the code as code, but as characters.
This process involves 3 steps: Create a template, create a prepared statement, and prepare a prepared statement. See details for all these steps below:
Create a template
Start by selecting data from the database. To do this, use a select statement where the user ID is equal to a placeholder name.
Create a prepared statement
Insert the prepared statement including the connection to the database within the parenthesis.
Prepare a prepared statement
Insert a MySQLi function for the prepared statement and check if the statement would work by testing it against the template. You can do this by inserting an IF statement, with the first condition being the prepared statement with $stmt as a parameter, and the second parameter being the SQL template. Remember to check for failure instead of success. You can do this by inserting an exclamation mark before the statement and an echo that the statement has failed.
Then, insert the ELSE statement by adding the bind parameters to the placeholder(s). Inside the parenthesis add three parameters: the prepared statement followed by a comma, the indicador for the placeholder in the template, and the actual data you want to insert instead of a placeholder. You can see the full solution in Multimedia Tutorials.
Hope you found these solutions useful. Let us know your experience with SQL injections in the comments.