SQL Injection to Web Application and prevention
What is SQL Injection?
SQL Injection is an attack that poisons dynamic SQL statements to comment out certain parts of the statement or appending a condition that will always be true. It takes advantage of the design flaws in poorly designed web applications to exploit SQL statements to execute malicious SQL code.
Data is one of the most vital components of information systems. Database-powered web applications are used by the organization to get data from customers. SQL is the acronym for Structured Query Language. It is used to retrieve and manipulate data in the database.
How SQL Injection Works
SQL Injection (SQLi) is a type of injection attack that makes it possible to execute malicious SQL statements. These statements control a database server behind a web application. Attackers can use SQL Injection vulnerabilities to bypass application security measures. They can go around authentication and authorization of a web page or web application and retrieve the content of the entire SQL database. They can also use SQL Injection to add, modify, and delete records in the database.
An SQL Injection vulnerability may affect any website or web application that uses an SQL database such as MySQL, Oracle, SQL Server, or others. Criminals may use it to gain unauthorized access to your sensitive data: customer information, personal data, trade secrets, intellectual property, and more. SQL Injection attacks are one of the oldest, most prevalent, and most dangerous web application vulnerabilities.
The types of attacks that can be performed using SQL injection vary depending on the type of database engine. The attack works on dynamic SQL statements. A dynamic statement is a statement that is generated at run time using parameters password from a web form or URI query string.
Let’s consider a simple web application with a login form. The code for the HTML form is shown below.
<form action=‘index.php’ method="post"> <input type="email" name="email" required="required"/> <input type="password" name="password"/> <input type="checkbox" name="remember_me" value="Remember me"/> <input type="submit" value="Submit"/> </form>
HERE,
- The above form accepts the email address, and password then submits them to a PHP file named index.php.
- It has an option of storing the login session in a cookie. We have deduced this from the remember_me checkbox. It uses the post method to submit data. This means the values are not displayed in the URL.
Let’s suppose the statement at the backend for checking user ID is as follows
SELECT * FROM users WHERE email = $_POST[’email’] AND password = md5($_POST[‘password’]);
HERE,
- The above statement uses the values of the $_POST[] array directly without sanitizing them.
- The password is encrypted using MD5 algorithm.
Let’s have a Simple example of SQL injection:
Simple SQL Injection Example
The first example is very simple. It shows, how an attacker can use an SQL Injection vulnerability to go around application security and authenticate as the administrator.
The following script is pseudocode executed on a web server. It is a simple example of authenticating with a username and a password. The example database has a table named users with the following columns: username and password.
# Define POST variables
uname = request.POST['username']
passwd = request.POST['password']
# SQL query vulnerable to SQLi
sql = “SELECT id FROM users WHERE username=’” + uname + “’ AND password=’” + passwd + “’”
# Execute the SQL statement
database.execute(sql)These input fields are vulnerable to SQL Injection. An attacker could use SQL commands in the input in a way that would alter the SQL statement executed by the database server. For example, they could use a trick involving a single quote and set the passwd field to:
password' OR 1=1How to Prevent SQL Injections (SQLi)
Preventing SQL Injection vulnerabilities is not easy. Specific prevention techniques depend on the subtype of SQLi vulnerability, on the SQL database engine, and on the programming language. However, there are certain general strategic principles that you should follow to keep your web application safe.
Preventing SQL Injection Attacks
Despite the significant dangers posed by SQLi attacks, they’re easy to prevent once you learn some secure coding best practices that include foundational procedures:
- Discover vulnerabilities
- Repair vulnerabilities
- Remediate vulnerabilities
- Mitigate impact
Testing is the key to discovering vulnerabilities in your code. Opt for robust tools like dynamic analysis (DAST) that looks at the app from the outside in as an attacker would, and static analysis tools (SAST) that look for vulnerabilities at the code level. Look for areas where your app connects to a database and try to pass it unusual values. For example, if you put in a value that contains a single quote, does your program treat that character as user data, or does it treat it as code? If you include a tautological test (like ‘ OR ‘1=1’) in your input, are you able to gain access as though you entered a valid password?
Once you have discovered vulnerabilities, it’s time to repair them. The best way to do this is by using parameters any time you need to make SQL queries to a database, entering placeholder values in your statements, and then passing user-inputted values to the statements at the time of execution. If your programming language does not support parameters, you can remediate your code by sanitizing or escaping input before passing it to a database. This lets your app know that user input is data rather than code it should execute.
Mitigation is also an important process to help reduce risk, but without addressing the underlying flaw. As an example, rather than looking to your app’s code, you might mitigate a flaw by examining database accounts used by your app and making sure that they have the smallest amount of privileges needed to read or insert data into your database.
Smart Developers, Secure Development
SQLi attacks can have severe consequences once threat actors have control, from your app or website going down to your customers’ confidential data being leaked.
Fortunately, SQLi attacks are easy to protect against. You need to look for vulnerabilities where attackers might inject their own SQL code and then repair those vulnerabilities using prepared statements, ORMS, and other strategies. If prepared statements are unavailable, vulnerabilities can be remediated by sanitizing or escaping user input before passing it to a database in a SQL query. Finally, enacting smart database access privileges can mitigate the number and severity of SQLi attacks before your app ever gets to executing queries.
