A quick answer to what "SQL Injection" is.

Version : 0.1
Date : 11/01/2012
By : Albert van der Sel
For who : For anyone who whishes a simple answer on this subject


This very short note will explain some essentials of "SQL Injection". This phenomenon is related to
a backend Database (like Oracle, SQL Server, MySQL and the like), and a Webserver or Application server
presenting the user a "form" with inputfields.

Section 1 will be extremely simple, but I am quite sure that the essential idea will get clear.


1. Quick Answer:


1.1 The most simplistic example:

Just imaging a user with a browser, who contacts a Webserver. The Webserver served an "inputform"
(of whatever kind) to this user, where the user can fill in some information. The webform might be some sort of
of "query form", used in order to retreive information. Once the form is filled in with values, the user
may submit the form to the Webserver (or Application Server).
On the Webserver, some scripting language, or other sort of code, uses standard SQL, and variables, which have as values the user supplied values, in order to construct
(one or more) SQL Statements. Those statements will then be sent to the backend Database.

- SQL Injection may now arise if the inputvalues have not been properly parsed and checked on sanity.
- Also, this "direct" method of concatenating SQL and user supplied values is not recommended.

⇒ Modify the values:

Suppose the Webform which the user filled in, was a "query webform" which makes it possible to request account information
from a certain "sloppy" Bank (it's just an example).
Now this Bank, sits very tight on IT expenses (due to bonuses to managers), and thus they hired a certain Albert
(surname will not be disclosed) to create a webapplication. So Albert went happily to work, and quickly
the application was finished.

Later, suppose a regular user wanted to view it's balance. So he filled in his account number and submitted the form.
The Webserver then uses a scripting language (or other code) with embedded SQL, so that a SQL statement can be constructed,
like for example:

SELECT * FROM ACCOUNTS WHERE acc_nr='@var_accnr'

In this example, the variable @var_accnr will hold the value that the user typed in. Suppose the user typed in
A23367BC as the accountnumber. So the variable @var_accnr will then hold that particular value too.
The SQL send to the database will then be:

SELECT * FROM ACCOUNTS WHERE acc_nr='A23367BC'

This query will then be sent to the Database, and the right results are returned to the user.

Now, note the quotes surrounding the @var_accnr variable, which denotes that the field acc_nr is of datatype varchar.

Let's see what SQL Injection can do in this case. Suppose a hacker, presented with the same form,
now types: NULL' OR 'a'='a.
If really no inputvalidation took place, the SQL send to the database will be:

SELECT * FROM ACCOUNTS WHERE acc_nr='NULL' OR 'a'='a'

This time, what the user the database asks is: return all records where acc_nr='NULL' OR where 'a'='a'.
Since 'a'='a' is always true, the database sees this Query as: just return all records.

You see? This time the user gets all records, thus also the records he was not supposed to get.

Sure, this was a ridiculous example (no Bank works this way), but it shows how it works:
We extended the input with additional SQL or SQL clauses (like: OR 'a'='a')

Ofcourse it's very simple to combat this example. If the accountnumber is 8 characters long, then make sure
that the @var_accnr variable is declared with the same lenght. It would be a bit weird if a developer had
declared this variable with, say, a length of 128 characters.

If the variables are of an appropriate lenght, and if somebody then types in a longer string,
then it won't "fit" into the variable. Then make sure an exception is raised, and an errorhandler is called,
which presents the user a message like:
"The accountnumber can only be 8 characters long. Please try again."

Ofcourse, this is not the same as parsing and thoroughly checking inputvalues.


⇒ Modify the URL:

Suppose that the variables used in the code are still "spacy" enough (and inputvar validation is still left out),
but the controls on the form are tight, without room to type extra clauses.
Instead of typing extra strings in the form, an unauthorized person might try to manipulate the browser address bar.

So, using the example above, if the address bar of the browser shows something like:

http://sloppybank.com/accounts.asp?acc_nr=A23367BC

he might try to modify that to something like:

http://sloppybank.com/accounts.asp?acc_nr=null'+%3e0+OR+'a'%3d'a

It's clear that validation of inputvalues is mandatory. Ofcourse this will be done by almost all developers,
and it's indeed true that developers are (since long) quite aware of those types of misuse.
But, don't forget: this is a very simple introduction to the subject, using quite exeggerated examples.


1.2 Quick (incomplete) check if a site is susceptible:

The following is certainly not a 100% fullproof method: At best, it's only an indicator.

In the unlikely event that you encounter an input form, and you are indeed able to enter
an extentable clause (given that the field on the form corresponds to a tablefield), like in the
example above, then various results may be observed.

- If you get back a neat result like "You query did not produced any results", or something similar,
it's an indicator that the input was indeed parsed.

- If you get back "Server error" or some html error page, it may indicate that the database indeed tried
to work with the supplied values, but it simply gave an error and the Webserver knew nothing else to
produce the "Server error" message or the html error page.

As said before: at best it can be viewed as an indicator.


1.3 Unauthorized persons will probably try a lot of "guessing":

Suppose the conditions for a hacker are truly perfect (it will never be that way).
You know, if such an inputvar is really large, and no sanity check is performed on the inputvalues,
then in an extreme case, even multiple statements may be send to the database.
If there is "space", the hacker can use the fact that a ";" symbol is a statement seperator, and so the hacker
might even send multiple statements to the database (it's an absurd example: I know).
So, instead of the simple " NULL' OR 'a'='a " clause, it might even be extended to:

" NULL' OR 'a'='a '; select table_name from user_tables "

where the unauthorized person has reasons to believe that the backend is Oracle, and the second part
is a legal Oracle query on the table names of the schema (owner) which the application uses.

If it would work, the unauthorized person knows all table names, and can proceed from there.

But No, it will never, and really never, be as simple as that.

In reality, if SQL Injection works, the unauthorized person probably has a lot of guessing to do,
unless he is very lucky.
In Weblogs, or other logs (like database logs), you might see many attempts originating from the
same IP or hostname.


1.4 Simple measurements:

Sanity checks on input can be done at:
  • At the client browser, using some scripting language (like javascript)
  • At the middletier, that is, the Webserver/Application Server
  • At the database, for example, the used SQL are stored procedures, which start with validation of input

Validation in the browser is often considered to be not the best place. Still, it almost always
will be implemented, but then it is often regarded as a sort of "pre-check", or just simply as a mechanism
to assist the user in avoiding entering wrong input.

Next, nobody can really proof that validation is "better" done at the Webserver or at the database.
It all depends also in the scripting/language used at the Middletier, and how the code in the database
is implemented. Logically, you would probably say that it is better to stop an attack in the "middle"
instead we are "already" at the database level.

Again I have to stress that the examples used in this note are exeggerated.

- In a business environment the identity of the user must be proven first, like using a username/password,
and often with additional security measurements like a smartcard, or a calculator
generating a challenge/reponse code. At least this ensures that only serious users, use the application.

- In other, more open environments, SQL Injection could be something to watch for.

Some simple obvious mesurements are:

  1. Try to avoid showing a browser address bar with variables, which can be modified.
  2. Design the form in such a way, that input is limited in length and type as to what the actual values could be.
  3. In the SQL, only use well defined variables, which uses a length and datatype that really
    corresponds to the actual table fields. Thus, avoid too spacy variables.
  4. Try to avoid "dynamic" SQL (more about that in section 2). In effect, it means never concatenate (add together)
    SQL with user input, to form SQL statements. This is a primary source for injection.
  5. Whenever possible, use "bind" variables (more about that in section 2)
  6. Parse the user supplied values (more about that in section 2)

Especially (4), creating statements using SQL and user supplied values directly, is not recommended.

These measurements would apply for any "custom" made Website (using php, asp etc..) which connect to a database.
But don't think you are all done if you use a large professional Web environment like Websphere, Oracle App Server,
or a Web/Collaboration environment like Sharepoint. Certainly not, if indeed all sorts of custom modules,
services, or webparts have been build, using SQL and usersupplied values, all concatenated together in the way
we already have seen above. And thus creating the same vulnerabilities.


This concludes this simple introduction. If you were really new to the subject, hopefully by now you got
an impresssion as to what SQL Injection means.