SQL Injection problem in TIBSQL

[maxi]

Hi,

I'm using TIBSQL 6.08, and I've a sql injection problem.

Let me expose an example:

I've a sql text in a string const,

const
SQL = 'SELECT USER_ID FROM USERS WHERE USER = ? AND PSWD = ?';


by other side, I do a params replace by change ? to PARAM_% (where % is
equal to 1, 2,...n, then a get


'SELECT USER_ID FROM USERS WHERE USER = :PARAM_1 AND PSWD = :PARAM_2';


After, I do a params assignement. Params are retrive in a variant array.

I create a TIBSQL on fly and do,

if VarIsArray(vParams) then
begin
for i := 0 to VarArrayHighBound(vParams, 1) do
ibSQL.Params[i].Value := vParams[i];
end;


Well, this params assignement allow sql injection.

If in the first param (PARAM_1) which is user name, I set,

'admin' or '1'='1'

the result SQL is

SELECT USER_ID FROM USERS WHERE USER = 'admin' or '1'='1' AND PSWD = ''

This is a several security issue.

How can I solve this?


Thanks in advance.

[Bill Todd [TeamB]]

Post by maxi
How can I solve this?

The obvious answer is do not let the user enter text that will be
inserted into an SQL statement without validation. If the user only
enters values and the values are assigned to parameters you are safe.

I assume you are doing what you are doing for some reason. Without
knowing what you are trying to accomplish there is no way to suggest a
safer alternative.

[maxi]

Post by Bill Todd [TeamB]

Post by maxi
How can I solve this?

The obvious answer is do not let the user enter text that will be
inserted into an SQL statement without validation. If the user only
enters values and the values are assigned to parameters you are safe.

Yes, of course, I don't let input text values to user directly. Values
are load into variant array and these are passed to sql string accross
TIBSQL params.
Remember, I do:

const
SQL = 'SELECT USER_ID FROM USERS WHERE USER = ? AND PSWD = ?';

After I change '?' to :PARAM format, then I get

new_sql = 'SELECT USER_ID FROM USERS WHERE USER = :PARAM_1 AND PSWD =
:PARAM_2';

Assign this to SQL property of TIBSQL (var IBSQL)

IBSQL.SQL.Text := new_sql

And assign params values (from variant array)
if VarIsArray(vParams) then
begin
for i := 0 to VarArrayHighBound(vParams, 1) do
ibSQL.Params[i].Value := vParams[i];
end;


I don't use strings concatenation for this purpose.
Is needed to scape string values anyway?
How?

---

It's different to do this in design time... ?

Put a TIBSQL in a form (TForm), assign in SQL property (accross Obj.
Inspector)

SELECT USER_ID FROM USERS WHERE USER = :PARAM_1 AND PSWD = :PARAM_2

Put two edits, one button and do in button click event...

IBSQL.Params[0].Value = Edit1.text; // User name
IBSQL.Params[1].Value = Edit2.text; // Password.
IBSQL.Execute;

(Sorry I don't test this last code)

Post by Bill Todd [TeamB]
I assume you are doing what you are doing for some reason. Without
knowing what you are trying to accomplish there is no way to suggest a
safer alternative.

Ok, that is part of my Middle tier application server. In this, I do
many SQL querys. I have a function, part of my server API, to execute
quickly sql statement. Somethig like

ExecSQL('SELECT A_FIELD1 FROM A_TABLE WHERE A_FIELD2 = ?',
[A_FIELD2_PARAM_VALUE]);

Internally, is doing this:

1. Change ? for :PARAM syntax format
2. Assign params values
3. Execute query



I hope be more clear.


Thanks for your help.
Regards.

[Bill Todd [TeamB]]

You have lost me. If you are not inserting user entered text into the
SQL statement where is the security risk. If you start with the
statement

'SELECT USER_ID FROM USERS WHERE USER = :PARAM_1 AND PSWD = :PARAM_2';

and assign the following param value

IBSQL.Params[0] := 'admin' or '1'='1';

the code will not compile.

If you use

IBSQL.Params[0] := QuotedStr('admin' or '1'='1');

you get a WHERE clause that compares the value of the field USER to the
literal constant 'admin' or '1'='1'. Parameters are not macro
substitution. When you use a parameterized SQL statement the statement
without any parameter values is sent to the database server where it is
parsed, optimized and compiled into an executable form. Then the
parameter values (which are sent to the server separately) are assigned
and the statement is executed. Values assigned to parameters are always
treated as literals. It is not possible to inject SQL via a parameter.

If this does not answer your question you need to provide actual code
that will compile and execute that shows how SQL can be injected by a
user.

[maxi]

Hi,

I can found the problem. My mistake, sorry :(

TIBSQL work fine, not sql injection is produced.

The problem was in my SQL params transform (internal function) to covert
"?" token to :PARAM format.

Thanks so much to Bill, Wayne and Jeff for your time.

Best Regards.

[Wayne Niddery (TeamB)]

Post by maxi
I've a sql text in a string const,
const
SQL = 'SELECT USER_ID FROM USERS WHERE USER = ? AND PSWD = ?';
by other side, I do a params replace by change ? to PARAM_% (where % is
equal to 1, 2,...n, then a get

Why is this necessary? Just start with:
SQL = 'SELECT USER_ID FROM USERS WHERE USER = :user AND PSWD = :pswd';

Post by maxi
ibSQL.Params[i].Value := vParams[i];
Well, this params assignement allow sql injection.
If in the first param (PARAM_1) which is user name, I set,
'admin' or '1'='1'
the result SQL is
SELECT USER_ID FROM USERS WHERE USER = 'admin' or '1'='1' AND PSWD = ''

If you are really using parameters then this is not possible. Assigning
'admin' or '1'='1' to an actual parameter would end up, on the server side
(not the client side) as:

SELECT USER_ID FROM USERS WHERE USER = ''admin' or '1'='1'' AND PSWD = ''

IOW, it would try to match the user to the entire string ''admin' or
'1'='1'', the string will NOT be turned into other conditions. SQL injection
is not possible if you actually use parameters.

[Jeff Overcash (TeamB)]

Post by Wayne Niddery (TeamB)

Post by maxi
I've a sql text in a string const,
const
SQL = 'SELECT USER_ID FROM USERS WHERE USER = ? AND PSWD = ?';
by other side, I do a params replace by change ? to PARAM_% (where %
is equal to 1, 2,...n, then a get

SQL = 'SELECT USER_ID FROM USERS WHERE USER = :user AND PSWD = :pswd';

Post by maxi
ibSQL.Params[i].Value := vParams[i];
Well, this params assignement allow sql injection.
If in the first param (PARAM_1) which is user name, I set,
'admin' or '1'='1'
the result SQL is
SELECT USER_ID FROM USERS WHERE USER = 'admin' or '1'='1' AND PSWD = ''

If you are really using parameters then this is not possible. Assigning
'admin' or '1'='1' to an actual parameter would end up, on the server
SELECT USER_ID FROM USERS WHERE USER = ''admin' or '1'='1'' AND PSWD = ''
IOW, it would try to match the user to the entire string ''admin' or
'1'='1'', the string will NOT be turned into other conditions. SQL
injection is not possible if you actually use parameters.

Why not just start with SQL like

const
SQL = 'SELECT USER_ID FROM USERS WHERE (USER = ? or ? = 'admin') AND PSWD
= ?';

and pass 3 parameters with he user name twice. Should that accomplish what he
is trying to do? Anything other than admin and you have to match a user.

[Ömür Ölmez]

You shoud do two thing :
1. Change to this :
const
SQL = 'SELECT USER_ID FROM USERS WHERE USER = ''?'' AND PSWD = ''?'' ';
2. Escape all ' characters with '' before set params. I mean like this,
str :=Edit1.Text;
str :=EscapeSQL(str);
ParamByName('p1').AsString :=str;
The idea is avoiding closing the open aposth by accidentally in sql text
just before sending !

In general, you should escape db specific chars in your user entries
before send them to db server.
In general, you should always escape and limit user entries regarding on
your interface. This is same thing with limiting filenames taken from user
as 8.3 and restricting entering words dir, copy, etc in DOS.

Regards
Ömür Ölmez

Post by maxi
Hi,
I'm using TIBSQL 6.08, and I've a sql injection problem.
I've a sql text in a string const,
const
SQL = 'SELECT USER_ID FROM USERS WHERE USER = ? AND PSWD = ?';
by other side, I do a params replace by change ? to PARAM_% (where % is
equal to 1, 2,...n, then a get
'SELECT USER_ID FROM USERS WHERE USER = :PARAM_1 AND PSWD = :PARAM_2';
After, I do a params assignement. Params are retrive in a variant array.
I create a TIBSQL on fly and do,
if VarIsArray(vParams) then
begin
for i := 0 to VarArrayHighBound(vParams, 1) do
ibSQL.Params[i].Value := vParams[i];
end;
Well, this params assignement allow sql injection.
If in the first param (PARAM_1) which is user name, I set,
'admin' or '1'='1'
the result SQL is
SELECT USER_ID FROM USERS WHERE USER = 'admin' or '1'='1' AND PSWD = ''
This is a several security issue.
How can I solve this?
Thanks in advance.

[Ömür Ölmez]

BTW, my answer depends Paulo's situation only; not for using parameters
assigment directly...

Post by Ömür Ölmez
const
SQL = 'SELECT USER_ID FROM USERS WHERE USER = ''?'' AND PSWD = ''?'' ';
2. Escape all ' characters with '' before set params. I mean like this,
str :=Edit1.Text;
str :=EscapeSQL(str);
ParamByName('p1').AsString :=str;
The idea is avoiding closing the open aposth by accidentally in sql
text just before sending !
In general, you should escape db specific chars in your user entries
before send them to db server.
In general, you should always escape and limit user entries regarding
on your interface. This is same thing with limiting filenames taken from
user as 8.3 and restricting entering words dir, copy, etc in DOS.
Regards
Ömür Ölmez

Post by maxi
Hi,
I'm using TIBSQL 6.08, and I've a sql injection problem.
I've a sql text in a string const,
const
SQL = 'SELECT USER_ID FROM USERS WHERE USER = ? AND PSWD = ?';
by other side, I do a params replace by change ? to PARAM_% (where % is
equal to 1, 2,...n, then a get
'SELECT USER_ID FROM USERS WHERE USER = :PARAM_1 AND PSWD = :PARAM_2';
After, I do a params assignement. Params are retrive in a variant array.
I create a TIBSQL on fly and do,
if VarIsArray(vParams) then
begin
for i := 0 to VarArrayHighBound(vParams, 1) do
ibSQL.Params[i].Value := vParams[i];
end;
Well, this params assignement allow sql injection.
If in the first param (PARAM_1) which is user name, I set,
'admin' or '1'='1'
the result SQL is
SELECT USER_ID FROM USERS WHERE USER = 'admin' or '1'='1' AND PSWD = ''
This is a several security issue.
How can I solve this?
Thanks in advance.