What to escape in AXL SQL queries

Version 1
    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.