[NEWS] Abusing Poor Programming Techniques in Web Server Scripts (SQL Statements)

From: support@securiteam.com
Date: 08/13/01 

From: support@securiteam.com
To: list@securiteam.com
Subject: [NEWS] Abusing Poor Programming Techniques in Web Server Scripts (SQL Statements)
Message-Id: <20010813083140.002E9138BF@mail.der-keiler.de>
Date: Mon, 13 Aug 2001 10:31:40 +0200 (CEST)

The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com

  Abusing Poor Programming Techniques in Web Server Scripts (SQL Statements)
------------------------------------------------------------------------

SUMMARY

This article is not about newly discovered vulnerabilities, but rather
this article tries to explain the different problems in many server-side
scripts and the ways of solving them.
The problem does not lie in the SQL server but rather in the SQL
statements that are passed to it without prior filtering of bad
characters. Without such filtering, it is possible in some case to bypass
the authentication mechanism that relies on the SQL statement for validity
checking.

This paper will focus on the problems that are generic as possible. The
paper was written by <mailto:memonix@roses-labs.com> Memonix of Roses
Labs.

DETAILS

When some code similar to this one is used in logins scripts:
 
  $login = Request.Form("login")
  $password = Request.Form("password")

  SELECT field FROM database WHERE Login=$login AND Password=$password
 
It is possible to "validate" a login without knowing the username and/or
the password.

For this to be possible (bypassing of the authentication mechanism) the
following perquisite, need to happen:
1. Select statement needs to be similar to this:

  SELECT filed FROM database WHERE Login=$login AND Password=$Password

Where $login and $password are form fields that the user filled.
 
2. Server scripts does not check both $login and $password for bad
characters (Such characters as ' ; ) " # | ).

3. Once validated, no additional checks are done on $login and $password
variables.

If all these conditions are met in the server, it is possible to bypass
the authentication mechanism or any other SQL statement.
 
Forced matching complete SQL statements:
Let examine a vulnerable SQL statement:

  SELECT filed FROM database WHERE Login=$login AND Password=$Password
 
Where $login and $password are the form fields that user fills with
provided data.
 
  SELECT filed FROM database WHERE Login='' AND Password=''

When no input is provided, the fields will be filled with an empty ''.
This limits the vulnerability to strings that terminate with ' (we need
that the SQL will be parsed without any syntax errors or the vulnerability
cannot be exploited).

A normal user login SQL would look something link this:
 
  SELECT field FROM database WHERE Login='Jon' AND Password='1234'

Where login equals john and password equals 1234.

If you insert as login the character ' , the SQL server will return a
SQL bad synatx error, since the syntax of the SQL statement is incorrect:

  SELECT field FROM database WHERE Login=''' AND Password=''
 
We can take care of that by making sure we close all the open statements
(i.e. add additional ' so that the query is complete) we can create a
valid SQL statement. For example:
 
If we replace the login field with: ' or ''=' and the password field with:
' or ''='. We will get from a statement:
 
  SELECT field FROM database WHERE Login=$login AND Password=$Password
 
The following result:

  SELECT field FROM database WHERE Login='' or ''='' AND Password= '' or
''=''

(NOTE: The SQL statement ' or ''='' ' will always return TRUE)
 
What will happen when we get TRUE for both the values?
The SQL statement will check in the database to verify whether we have
provided the right username and password. The SQL statement will return
TRUE because both the "or" logical check will always return TRUE, this
would of course result in the program thinking we have provided a valid
username and password combination. Therefore, the server script, if it
does no more checks of the validity of the username and password, will
allow the user to be logged-on as the first user on the database (usually
the first user in the database is the database administrator account).

Forced matching partial SQL statements:
As explained above, there are many ways to skip the login validation if
all previous conditions are present in the login script.

It is also possible to match partial SQL statements, this would allow a
more specific attack (you can logon to a specific user instead of trying
to logon to random user, as described in the example above). For example:
 
It is possible to gain access as a known user by only knowing its
username.

For example:

Setting the login field to Jonn and the password field to ' or ''=' will
match the user Jonn with any password it has (since the second part of the
statement will always return TRUE).

Make SQL ignore parts of the SQL statement:
It is possible to cause the SQL server to ignore parts of the SQL
statement by including in the parsed SQL statement the strings /* and */
 
For example:

Setting the login field to '/* and the password field to */ OR '' = '

Will cause the SQL statement to result in:

  SELECT field FROM database WHERE Login = ''/*' AND Password = '*/ OR ''
= ''
  
SQL statement within the /* */ characters will be avoided by the SQL
server, causing the SQL statement to be treated as:
 
  SELECT FROM DATABASE WHERE Login = '' OR '' = ''

This statement is always true.
 
Here is another example, sometimes the email address is checked as the
login name. It is possible to create a valid login that passes both the
SQL statement and the script email validation:
 
Setting the login field to '/*mi@mail.com and the password field to */ or
''=' .
 
The server script will parse the $login variable for a valid email such
structure *@*.*, and SQL will parse it avoiding the comments (/* */),
thus allowing us to login.
 
Solution:
There are several ways to stop these vulnerabilities. One way is to use
JavaScript, but these checks can be skipped by manually posting the
information to the CGI.

Here is an example for such a Javascript:
 
<SCRIPT LANGUAGE="JavaScript">
function checkstring(text){
         pat=/^[A-Za-z0-9]{6,10}$/;
         result=text.match(pat);
         return TRUE;
}

function Send(){
         if (checkstring(txtName) && checkstring(txtPassword)){
             login.submit();
         }
}
</SCRIPT>

Here is an example for stopping these vulnerabilities via PHP code:

Before (no checks are done):
 
  if ($luser!="" && $lpassword!="") {
  $login_rs = @mysql_query("SELECT * FROM cro_user WHERE
  user_login='$luser' AND user_password='$lpassword' AND user_status!='0'
  LIMIT 0,1",$db);
  if (@mysql_num_rows($login_rs)==0) {
         echo "LOGGED ON";
         }
  }
 
To bypass the username and password protection you can pass in the login
field '| and in the password field '|
 
Here is a fix for this code

After (checks are done):
 
  $luser = trim(htmlspecialchars(addslashes($luser)));
  $lpassword = trim(htmlspecialchars(addslashes($lpassword)));
 
  if ($luser!="" && $lpassword!="") {
  $login_rs = @mysql_query("SELECT * FROM cro_user WHERE
  user_login='$luser' AND user_password='$lpassword' AND user_status!='0'
  LIMIT 0,1",$db);
  if (@mysql_num_rows($login_rs)==0) {
         echo "LOGGED ON";
         }
  }
 
All the special characters in the strings will be dropped in by the SQL.
The idea it to either delete, or escape any dangerous characters such as:
 
() / , ; . : # <> | \ " .... All but letters and numbers.
 
It is recommended that the dangerous characters are dropped in the server
side of the code, and not on the client side.

ADDITIONAL INFORMATION

The information has been provided by <mailto:memonix@roses-labs.com>
Memonix of Roses Labs.

========================================

This bulletin is sent to members of the SecuriTeam mailing list.
To unsubscribe from the list, send mail with an empty subject line and body to: list-unsubscribe@securiteam.com
In order to subscribe to the mailing list, simply forward this email to: list-subscribe@securiteam.com

====================
====================

DISCLAIMER:
The information in this bulletin is provided "AS IS" without warranty of any kind.
In no event shall we be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages.

Relevant Pages

  • Re: sql call not working but should!
    ... > I have this login routine that I can't seem to get the Sub loginpartner to ... The sql statement for loginpartner has been ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: how sql injection is possible ?
    ... Suppose you have a badly designed login script: ... // successful login attempt ... If someone were to attempt to login using the username: ... This would make the SQL statement now: ...
    (comp.lang.php)
  • Re: Hacker activity?
    ... >login to a server, most as root but some are attempts to login to ... >telnet, all come from the same remote server, and all fail. ... >getting some odd cgi calls to a script on a secure ssl server. ... Make sure root cannot login to your system via ssh. ...
    (freebsd-questions)
  • Re: Limit desktop & start menu
    ... Create a login script that runs when users log into the TS, and map the R: ... persisitent "R" drive on the server itself and that may cure it. ...
    (microsoft.public.windows.terminal_services)
  • Re: Slow TS logon from dumb terminals.
    ... Is a login from the console also slow? ... Do the users run a login script? ... if the Real-time protection component runs in every user session. ... No virus protection as only the server has s CD Rom/USB Access/Floppy. ...
    (microsoft.public.win2000.termserv.clients)