Skip to content

How to protect your database code from SQL injections

Preamble

One of my colleague has been caught by a company doing internal ethical hacking on our home made applications (penetration test). He got challenged on the code of his application sensible on SQL injections and the ethical hacker has been able to see more than what was expecting

This application is using MariaDB as the database backend and we had a brief look together. Application is made of multiple procedures where parameters are concatenated to build a string variable that is executed with something like:

prepare statement from @v_sql;
execute statement;

Of course to prevent from SQL injections you must use:

execute statement using param1, param2, ..;

I have never experimented it and I wanted to see how you are simply covered (or not) with this USING keyword and also see how it works with Oracle that is hugely used where I work…

Testing has been done using MariaDB 11.8.2 and Oracle Enterprise Edition 21.18 running on my test Linux running Red Hat Enterprise Linux release 8.10 (Ootpa) with 12 cores and 64GB of memory.

sql_injection01
sql_injection01

Data model

I have used https://generatedata.com/ to create my small test case that is a simple table with an id a name, I have also, automatically, inserted 100 rows:

drop table if exists users;

create table users (
  id mediumint(8) unsigned not null auto_increment,
  name varchar(255) default null,
  primary key (id)
) auto_increment=1;

Or for Oracle:

drop table users;

create table users 
(
  id number generated always as identity increment by 1 start with 1 not null,
  name varchar2(255),
  constraint users_pk primary key (id) enable 
);

MariaDB

SQL injections

Inspired by the current state of our application:

delimiter 
create or replace procedure yannick_execute_sql_with_sql_injection(in param1 varchar(100))
begin
  declare found_rows_var int;
  declare v_sql varchar(100);
  select concat('select * from test.users where id=', param1) into @v_sql;
  prepare stmt from  @v_sql;
  execute stmt;
  select found_rows() into @found_rows_var;
  deallocate prepare stmt;
  select concat('Found_rows = ', @found_rows_var) as debug;
end;
//
delimiter ;

If I execute gently the code with a number or a string the conversion is implicit:

MariaDB [test]> call yannick_execute_sql_with_sql_injection(11);
+----+-------------+
| id | name        |
+----+-------------+
| 11 | Dale Benton |
+----+-------------+
1 row in set (0.001 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 1 |
+----------------+
1 row in set (0.002 sec)

Query OK, 2 rows affected (0.002 sec)

MariaDB [test]> call yannick_execute_sql_with_sql_injection('11');
+----+-------------+
| id | name        |
+----+-------------+
| 11 | Dale Benton |
+----+-------------+
1 row in set (0.001 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 1 |
+----------------+
1 row in set (0.001 sec)

Query OK, 2 rows affected (0.001 sec)

If I start to sneak then it goes wild immediately:

MariaDB [test]> call yannick_execute_sql_with_sql_injection('11 or 1═1');
+-----+----------------------+
| id  | name                 |
+-----+----------------------+
|   1 | Lila Cook            |
|   2 | Oscar Buckner        |
.
.
.
|  99 | Sydnee Vazquez       |
| 100 | Jana Woodard         |
+-----+----------------------+
100 rows in set (0.001 sec)

+------------------+
| debug            |
+------------------+
| Found_rows = 100 |
+------------------+
1 row in set (0.002 sec)

Query OK, 2 rows affected (0.002 sec)

So clearly the code is not secure…

No SQL injections

Now moving to best practices with USING keyword:

delimiter //
create or replace procedure yannick_execute_sql_without_sql_injection(in param1 varchar(100))
begin
  declare found_rows_var int;
  declare v_sql varchar(100);
  set @v_sql = "select * from test.users where id=?";
  prepare stmt from  @v_sql;
  execute stmt using param1;
  select found_rows() into @found_rows_var;
  deallocate prepare stmt;
  select concat('Found_rows = ' , @found_rows_var) as debug;
end;
//
delimiter ;

Gentle execution:

MariaDB [test]> call yannick_execute_sql_without_sql_injection('11');
+----+-------------+
| id | name        |
+----+-------------+
| 11 | Dale Benton |
+----+-------------+
1 row in set (0.001 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 1 |
+----------------+
1 row in set (0.001 sec)

Query OK, 1 row affected (0.002 sec)

If you try to inject bad SQL then, by default, with no effort you are protected:

MariaDB [test]> call yannick_execute_sql_without_sql_injection('11 or 1═1');
+----+-------------+
| id | name        |
+----+-------------+
| 11 | Dale Benton |
+----+-------------+
1 row in set (0.001 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 1 |
+----------------+
1 row in set (0.001 sec)

Query OK, 1 row affected, 1 warning (0.001 sec)

MariaDB [test]> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '11 or 1═1' |
+---------+------+------------------------------------------------+
1 row in set (0.000 sec)

And this is really the added value of this using keyword, all the hard work to prevent from SQL injections is done by default !

If the parameter is for a varchar column

We noticed just above the Truncated incorrect DECIMAL value: ’11 or 1═1′ warning message, so asking myself what if the parameter is not for a number column ?

delimiter //
create or replace procedure yannick_execute_sql_with_sql_injection(in param1 varchar(100))
begin
  declare found_rows_var int;
  declare v_sql varchar(100);
  select concat("select * from test.users where name='", param1,"'") into @v_sql;
  prepare stmt from  @v_sql;
  execute stmt;
  select found_rows() into @found_rows_var;
  deallocate prepare stmt;
  select concat('Found_rows = ', @found_rows_var) as debug;
end;
//
delimiter ;

Gentle execution:

MariaDB [test]> call yannick_execute_sql_with_sql_injection('Dale Benton');
+----+-------------+
| id | name        |
+----+-------------+
| 11 | Dale Benton |
+----+-------------+
1 row in set (0.002 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 1 |
+----------------+
1 row in set (0.002 sec)

Query OK, 2 rows affected (0.002 sec)

Injecting in this procedure is not so easy. You have to close he single quote and put the # character to comment out the last single quote:

MariaDB [test]> call yannick_execute_sql_with_sql_injection("Dale Benton' or 1═1#");
+-----+----------------------+
| id  | name                 |
+-----+----------------------+
|   1 | Lila Cook            |
|   2 | Oscar Buckner        |
.
.
.
|  99 | Sydnee Vazquez       |
| 100 | Jana Woodard         |
+-----+----------------------+
100 rows in set (0.001 sec)

+------------------+
| debug            |
+------------------+
| Found_rows = 100 |
+------------------+
1 row in set (0.002 sec)

Query OK, 2 rows affected (0.002 sec)

The procedure following security best practices:

delimiter //
create or replace procedure yannick_execute_sql_without_sql_injection(in param1 varchar(100))
begin
  declare found_rows_var int;
  declare v_sql varchar(100);
  set @v_sql = "select * from test.users where name=?";
  prepare stmt from  @v_sql;
  execute stmt using param1;
  select found_rows() into @found_rows_var;
  deallocate prepare stmt;
  select concat('Found_rows = ' , @found_rows_var) as debug;
end;
//
delimiter ;

Normal execution:

MariaDB [test]> call yannick_execute_sql_without_sql_injection('Dale Benton');
+----+-------------+
| id | name        |
+----+-------------+
| 11 | Dale Benton |
+----+-------------+
1 row in set (0.002 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 1 |
+----------------+
1 row in set (0.002 sec)

Query OK, 1 row affected (0.002 sec)

The injection trial is also failing, as expected, but here MariaDB does not try to execute with only a part of the parameter (would be too complex) and the query is simply returning nothing:

MariaDB [test]> call yannick_execute_sql_without_sql_injection("Dale Benton' or 1═1#");
Empty set (0.001 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 0 |
+----------------+

Isn’t it better to work dirty ?

MariaDB allow to mix SQL and procedural language. That would not be possible with Oracle except if you do SELECT INTO but it would be for single row query only. So we tried not using at all all those PREPARE/EXECUTE statements and surprisingly it works very well:

delimiter //
create or replace procedure yannick_execute_sql_with_simple_select(in param1 varchar(100))
begin
  declare found_rows_var int;
  declare v_sql varchar(100);
  select * from test.users where name=param1;
  select found_rows() into @found_rows_var;
  select concat('Found_rows = ', @found_rows_var) as debug;
end;
//
delimiter ;

Injection are automatically detected:

MariaDB [test]> call yannick_execute_sql_with_simple_select("Dale Benton");
+----+-------------+
| id | name        |
+----+-------------+
| 11 | Dale Benton |
+----+-------------+
1 row in set (0.001 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 1 |
+----------------+
1 row in set (0.001 sec)

Query OK, 1 row affected (0.002 sec)

MariaDB [test]> call yannick_execute_sql_with_simple_select("Dale Benton' or 1═1#");
Empty set (0.001 sec)

+----------------+
| debug          |
+----------------+
| Found_rows = 0 |
+----------------+
1 row in set (0.001 sec)

Query OK, 1 row affected (0.001 sec)

Oracle

SQL injections

The equivalent Oracle procedure is:

create or replace procedure yannick_execute_sql_with_sql_injection(param1 in varchar2)
is
found_rows_var number;
v_sql varchar2(100);
type empcurtyp  is ref cursor;
cursor01 empcurtyp;
item01 users%rowtype;
begin
v_sql := 'select * from users where id=' || param1;
open cursor01 for v_sql;
loop
fetch cursor01 into item01;
exit when cursor01%notfound;
dbms_output.put_line('id = ' ||item01.id || ', name = ' || item01.name);
end loop;
dbms_output.put_line('Found rows = ' || cursor01%rowcount);
close cursor01;
end;

Going faster, gentle and SQL injected execution:

SQL> set serveroutput on size 999999
SQL> exec yannick_execute_sql_with_sql_injection('11');
id = 11, name = Erasmus Hoffman
Found rows = 1

PL/SQL procedure successfully completed.

SQL> exec yannick_execute_sql_with_sql_injection('11 or 1═1');
id = 1, name = Brittany Atkins
id = 2, name = Theodore Whitfield
.
.
.
id = 99, name = Cora Duncan
id = 100, name = Arsenio Pollard
Found rows = 100

PL/SQL procedure successfully completed.

No SQL injections

Procedure is now:

create or replace procedure yannick_execute_sql_without_sql_injection(param1 in varchar2)
is
  found_rows_var number;
  v_sql varchar2(100);
  type empcurtyp  is ref cursor;
  cursor01 empcurtyp;
  item01 users%rowtype;
begin
  v_sql := 'select * from users where id=:id';
  open cursor01 for v_sql using param1;
  loop
    fetch cursor01 into item01;
    exit when cursor01%notfound;
    dbms_output.put_line('id = ' ||item01.id || ', name = ' || item01.name);
  end loop;
  dbms_output.put_line('Found rows = ' || cursor01%rowcount);
  close cursor01;
end;

We see we are perfectly protected with no effort:

SQL> exec yannick_execute_sql_without_sql_injection('11 or 1═1');
BEGIN yannick_execute_sql_without_sql_injection('11 or 1═1'); END;

*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "YJAQUIER.YANNICK_EXECUTE_SQL_WITHOUT_SQL_INJECTION", line 10
ORA-06512: at line 1

If the parameter is for a varchar2 column

The procedure using a where clause with a varchar2 column:

create or replace procedure yannick_execute_sql_without_sql_injection(param1 in varchar2)
is
  found_rows_var number;
  v_sql varchar2(100);
  type empcurtyp  is ref cursor;
  cursor01 empcurtyp;
  item01 users%rowtype;
begin
  v_sql := 'select * from users where name=:name';
  open cursor01 for v_sql using param1;
  loop
    fetch cursor01 into item01;
    exit when cursor01%notfound;
    dbms_output.put_line('id = ' ||item01.id || ', name = ' || item01.name);
  end loop;
  dbms_output.put_line('Found rows = ' || cursor01%rowcount);
  -- close cursor:
  close cursor01;
end;

Similar behavior than MariaDB:

SQL> exec yannick_execute_sql_without_sql_injection('Erasmus Hoffman');
id = 11, name = Erasmus Hoffman
Found rows = 1

PL/SQL procedure successfully completed.

SQL> exec yannick_execute_sql_without_sql_injection('Erasmus Hoffman'' or 1═1');
Found rows = 0

PL/SQL procedure successfully completed.

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *