This document was generated from CDN thread
Created by: Gordon Ross on 28-09-2009 08:09:33 AM
I'm using the AXL SQL interface(s). Apart from normal XML character escaping, do I need to do any other escaping of characters, to prevent any form of SQL hacking ?
Thanks,
GTG
Subject: RE: What to escape in AXL SQL queries
Replied by: David Staudt on 28-09-2009 01:20:36 PM
Escaping XML reserved characters should be enough.
Note, <executeSQLQuery> can be 'tricked' into performing SQL updates. You will not want to let casual end users submit their own SQL queries, to be executed blindly:
select first 1 name from device; delete * from device;
The aboe will pass the check in <executeSQLQuery> checking for SELECTs only.
Subject: RE: What to escape in AXL SQL queries
Replied by: Gordon Ross on 28-09-2009 01:57:04 PM
I'm not looking to let people execute their own complete SQL statements. However, users input will be fed into SQL commands as a parameters, so I want to make sure I'm not opening any holes.
e.g.
SELECT tkpatternusage FROM numplan WHERE dnorpattern = 'USER_INPUT'
Where USER_INPUT will ultimately be supplied by an end user. NOTE: In this example, yes i can sanity check to make sure only numbers are being passed. However, other queries will involve strings e.g. Names !
GTG
Subject: RE: What to escape in AXL SQL queries
Replied by: Stephan Steiner on 29-09-2009 01:22:54 PM
David... isn't that (abusing the select to make update) something that should be blocked in AXL since it poses a security risk? I figured if there's an update command, it should be the only one allowed to actually make updates.
Subject: RE: What to escape in AXL SQL queries
Replied by: David Staudt on 29-09-2009 02:57:15 PM
You will want to validate the string input, too, based on the allowed characters for the field. A possible attack on your sql might look like:
SELECT tkpatternusage FROM numplan WHERE dnorpattern = 'USER_INPUT'
where USER_INPUT: '+CAST((delete * from device) as varchar(20))+'
giving an executed query of:
SELECT tkpatternusage FROM numplan WHERE dnorpattern = ''+CAST((delete * from device) as varchar(20))+''
Subject: RE: What to escape in AXL SQL queries
Replied by: David Staudt on 29-09-2009 03:04:12 PM
The restriction/check for <executeSQLQuery> was always intended as a 'guard rail' to encourage developers to go with the intended usage, rather than as a real secure lockdown mechanism. Given that the AXL user has full read/write acces, an app can already destroy the DB without resorting to getting around <executeSQLQuery>.
Subject: RE: What to escape in AXL SQL queries
Replied by: Gordon Ross on 29-09-2009 03:42:23 PM
As a first level defense, I am stripping out single & double quotes, commas and semicolons out of user input. After that I'll then try and do some more field-specific filtering.
