How to avoid SQL injection risks

1

How does one avoid SQL injection risks, in Clarion, apart from using a SQL function and passing parameters to the function?

Get SUM from MSSQL in Clarion
2

The new SQL() function is by far the easiest approach. Itā€™s the same form as the prop:sql, and uses the same NEXT loop to to read. Converting prop:sql to SQL() is trivial to do and requires the minimum of change.

Assuming you donā€™t have the SQL() function (yet?) our best option is two fold;

a) Avoid prop:Sql wherever possible. Make use of appropriate Clarion driver commands instead. Thereā€™s actually a lot more in there than you propbably expect - most people are using prop:Sql in places where itā€™s not necessary. Itā€™s an easy pattern and so something quickly reached for, even if the driver offers a safe alternative.

b) you can sanitise your input. ie before adding anything to prop:sql. Unfortunately this is somewhat of a wack-a-mole approach, but it is a LOT better than nothing, and will get stronger as you improve your sanitize function.

Both options are less than ideal because weā€™re all lazy, and when thereā€™s no immediate tangible benefit to customers itā€™s easy to just reach for prop:sql. Itā€™s a lot less work to just ignore security (which is why most programmers, in most languages to be fair, simply ignore it.)

Hence the SQL() command - designed to be as easy as prop:Sql, but at the same time offer better security for no extra effort.

Get SUM from MSSQL in Clarion
3

Thanks Bruce.

Where is this new SQL() command available and what is different in terms of what is sent to the database?

4

Search for prop:sql in this docs page. Discussed in many sections (138 hits in chrome search).

https://www.capesoft.com/docs/Driverkit/ClarionObjectBasedDrivers.htm#sqlexecute

1 Like
5

In addition to what Bruce said, there are some other things you can do. Firstly, the really risky situation is when the user is able to type in text that gets put into a prop:sql, like "SELECT whatever from sometable where city = ā€™ & ā€˜Sucker; delete * from important_table;ā€™ where the stuff in bold is the user input. Much less risky if the user input is being collected in numeric or date fields.
Chance of nasty damage is also much lower if the userā€™s credentials on the database are not that great. You put your data in app_schema, your user logs on with their own username, and their username is granted rights on the stuff in app_schema via grants to a role; they can only do a select on data in some tables, can only update or insert in others, not allowed to delete. Certainly no DML.
One thing I often do, particularly for putting stuff in where clauses, is to use a temporary table. Say you want stuff between two dates. You set up a temporary table (date_limits) that has start_date and end_date. You set up a view that is something like:

create or replace sometable_view as
select <columns>
from some_table t
join date_limits l on t.dt between l.start_date and l.end_date

Your user-entered date_limits go into the temporary table, and then your browse is run off the view. Avoids PROP:SQL, and the dates are guaranteed to be dates.
Also, of course, no real risk of SQL injection if the user is not entering any of the information. For example, I might have a clarion form that updates something the normal clarion way, and have a prop:SQL when the form is accepted that sends:
update table_status set status = 'Modified', dt = sysdate where id = ' & tab:id

1 Like
6

Much more info on SQL Injection in Clarion in the new File Driver Kit docs

https://capesoft.com/docs/Driverkit/ClarionObjectBasedDrivers.htm#sqlinjection

Wikipedia on SQL injection

7

Hi Bruce,

Do you mean the Clarion SQL() function? Not exactly new, I think itā€™s been around for 20 years or so - I think it was introduced in C5.5, it is in the C6.3 documentation from 2007. It only worked for VIEWS if I recall, you could not use it directly on files.

Best regards,

1 Like
8

Not the SQL() function but the Prop:SQL.

A new command, SQL, has been introduced as a safer approach to calling embedded SQL statements. This replaces the need for the prop:Sql command, which is inherantly vulnerable to SQL Injection attacks.

Capesofts SQL() functions seems alot like the SV SQL() function imo, with added text to highlight why its preferable to use instead of Prop:SQL, which if hooked up to a (c)string text control on a window in an app somewhere becomes a Little Bobby Drop Tables.

9

Hi Arnor. Same name, but different function.

The Clarion SQL(string) function is used to embed SQL Language directly into a prop:filter or prop:order. As you noted it is limited to Views. You also need to figure out, and use, the SQL Alias correctly. The new drivers continue to support this (although the Alias restriction has fallen way.) And the new drivers support this for FIles as well as Views.

But no, thatā€™s not the SQL function Iā€™m refering to. The new drivers provide a new command also called SQL, but itā€™s called with a FILE or VIEW parameter. As in
SQL(somefile, somesql, <parm1>,,,<parm20>)
This allows you to parameterise a sql command, thus making it safe.

So if you have a prop:Sql that read something like

Customers{prop:sql} = 'SELECT name from Customers where Cus:Name > ''' & someLocator & '''')

This becomes

SQL(Customers,'SELECT name from Customers where Cus:Name > ?1',somelocator)

The second form is simpler (no need to deal with quotes) and also safer, because the contents of someLocator as passed to the SQL engine as a parameter. So itā€™s never ā€œcompiledā€ by the SQL engine, and thus is immune to SQL Injection attacks.

For a better explanation, and more examples see;
https://www.capesoft.com/docs/Driverkit/ClarionObjectBasedDrivers.htm#sqlexecute

1 Like
10

Hi Richard,

No, there really is a big difference because you are using bound parameters. In PROP:SQL you are saying to the server: here is a command or a bunch of commands I want you to carry out. Interpret the cstring I have sent you and work out what to do with it. If you send it:

select * from whatever where somecol = 'myvalue'; delete from some_table;

it will treat it as two commands, with the second one being the deletion of everything in the table.

If you send it:

select * from whatever where somecol = ?1

where ?1 is a bound string, then it will look for values where somecol is greater than whatever you send in the string; it isnā€™t trying to parse the whole resulting string to see if there might be another command or commands in there.

In terms of how this works in the database, what you are doing is something like this (this is Oracle PL/SQL, but same concepts apply to TSQL etc.: )

declare
starting_activity varchar2(40);

cursor x is 
select activity_id
from activity
where activity_name > starting_activity;

begin
  starting_activity := 'LLL; delete from a;';
  for y in x loop
    dbms_output.put_line('ID is ' || y.activity_id);
  end loop;  
end;

Starting_activity is your user input. If you use prop:SQL you can potentially send to the server, if your malicious user manages to handle the quotes correctly:

select activity_id
from activity
where activity_name > 'LLL'; delete from a;

and you lose your A table.

If starting_activity is a bound cstring, then the select statement can only be:

select activity_id
from activity
where activity_name > 'LLL; delete from a;'

which is harmless.

1 Like
11

The SV SQL command is used inside a prop:filter or prop:order. It isnā€™t used for whole sql statements. Itā€™s very different to the driver kit SQL(file) command.

12

Hi Bruce,

Ah, so itā€™s part of your new drivers. Cool.

13

Hi Bruce,

Or did I miss something - is this now in Clarion? Iā€™m primarily still working in C9.1, so Iā€™m ages behind :wink:

Back in 2024 my main client and I went through a big effort to bring a large PHP/MySQL project up to full parameterized queries for penetration testing. PHP makes it fairly easy, but it was a bit of an effort to convert older code up to parameterized queries.

I have used /TURBOSQL quite a bit in the past, but as I recall there was no option for parameters for queriesā€¦

Best regards,

14

Its in the Driver Kit Arnor. Works in Clarion 9.1 and up.

15

this is a good articule but very complex to integrate to clarion

1 Like