9.4 SQL injection

What is SQL injection

SQL injection attacks are (as the name would suggest) one of the many types of script injection attacks. In web development, these are the most common form of security vulnerabilities. Attackers can use it to obtain sensitive information from databases, and aspects of an attack can involve adding users to the database, exporting private files, and even obtaining the highest system privileges for their own nefarious purposes.

SQL injection occurs when web applications do not effectively filter out user input, leaving the door wide open for attackers to submit malicious SQL query code to the server. Applications often receive injected code as part of an attacker's input, which alters the logic of the original query in some way. When the application attempts to execute the query, the attacker's malicious code is executed instead.

SQL injection examples

Many web developers do not realize how SQL queries can be tampered with, and may hold the misconception that they are trusted commands. As everyone knows, SQL queries are able to circumvent access controls, thereby bypassing the standard authentication and authorization checks. What's more, it's possible to run SQL queries through commands at the level of the host system.

Let's have a look at some real examples to explain the process of SQL injection in detail.

Consider the following simple login form :

<form action="/login" method="POST">
<p>Username: <input type="text" name="username" /></p>
<p>Password: <input type="password" name="password" /></p>
<p><input type="submit" value="Login" /></p>
</form>

Our form processing might look like this:

username := r.Form.Get("username")
password := r.Form.Get("password")
sql := "SELECT * FROM user WHERE username='" + username + "' AND password='" + password + "'"

If the user inputs a user name or password as:

myuser' or 'foo' = 'foo' --

Then our SQL becomes the following:

SELECT * FROM user WHERE username='myuser' or 'foo' = 'foo' --'' AND password='xxx'

In SQL, anything after -- is a comment. Thus, inserting the -- as the attacker did above alters the query in a fatal way, allowing an attacker to successfully login as a user without a valid password.

Far more dangerous exploits exist for MSSQL SQL injections, and some can even perform system commands. The following examples will demonstrate how terrible SQL injections can be in some versions of MSSQL databases.

sql := "SELECT * FROM products WHERE name LIKE '%" + prod + "%'"
Db.Exec(sql)

If an attacker submits a%' exec master..xp_cmdshell 'net user test testpass /ADD' -- as the "prod" variable, then the sql will become

sql := "SELECT * FROM products WHERE name LIKE '%a%' exec master..xp_cmdshell 'net user test testpass /ADD'--%'"

The MSSQL Server executes the SQL statement including the commands in the user supplied "prod" variable, which adds new users to the system. If this program is run as is, and the MSSQLSERVER service has sufficient privileges, an attacker can register a system account to access this machine.

Although the examples above are tied to a specific database system, this does not mean that other database systems cannot be subjected to similar types of attacks. The principles behind SQL injection attacks remain the same, though the method with which they are perpetrated may vary.

How to prevent SQL injection

You might be thinking that an attacker would have to know information about the target database's structure in order to carry out an SQL injection attack. While this is true, it's difficult to guarantee that an attacker won't be able to find this information and once they get it, the database can be compromised. If you are using open source software to access the database, such as a forum application, intruders can easily get the related code. Obviously with poorly designed code, the security risks are even greater. Discuz, phpwind and phpcms are some examples of popular open source programs that have been vulnerable to SQL injection attacks.

These attacks happen to systems where safety precautions are not prioritized. We've said it before, we'll say it again: never trust any kind of input, especially user data. This includes data coming from selection boxes, hidden input fields or cookies. As our first example above has shown, even supposedly normal queries can cause disasters.

SQL injection attacks can be devastating -how can do we even begin to defend against them? The following suggestions are a good starting point for preventing SQL injection:

  1. Strictly limit permissions for database operations so that users only have the minimum set of permissions required to accomplish their work, thus minimizing the risk of database injection attacks.
  2. Check that input data has the expected data format, and strictly limit the types of variables that can be submitted. This can involve regexp matching, or using the strconv package to convert strings into other basic types for sanitization and evaluation.
  3. Transcode or escape from pairs of special characters ( '"\&*; etc. ) before persisting them into the database. Go's text/template package has a HTMLEscapeString function that can be used to return escaped HTML.
  4. Use your database's parameterized query interface. Parameterized statements use parameters instead of concatenating user input variables in embedded SQL statements; in other words, they do not directly splice ​​SQL statements. For example, using the the Prepare function in Go's database/sql package, we can create prepared statements for later execution with Query or Exec(query string, args... interface {}).
  5. Before releasing your application, thoroughly test it using professional tools for detecting SQL injection vulnerabilities and to repair them, if they exist. There are many online open source tools that do just this, such as sqlmap, SQLninja, to name a few.
  6. Avoid printing out SQL error information on public webpages. Attackers can use these error messages to carry out SQL injection attacks. Examples of such errors are type errors, fields not matching errors, or any errors containing SQL statements.

Summary

Through the above examples, we've learned that SQL injection is a very real and very dangerous web security vulnerability. When we write web application, we should pay attention to every little detail and treat security issues with the utmost care. Doing so will lead to better and more secure web applications, and can ultimately be the determing factor in whether or not your application succeeds.

results matching ""

    No results matching ""