There I was, in full flow, explaining to the PHB how SQL injection cannot be applied to a webspeed application (he read it somewhere in some bloody newspaper) when the thought crossed my mind that I may be talking out of my posterior.
Am I wrong in my deluded belief ?
Julian
SQL injection has two main (http://en.wikipedia.org/wiki/SQL_injection) results:
1. seeing more data than you should
2. corrupting data
1. If you allow users to enter search criteria that are used unsanitized in QUERY-PREPARE phrases then the user can trick the query into returning all records by entering ' " OR TRUE '
2. however the user will never be able to trick a read query into DELETING records, since this is not part of the QUERY-PREPARE phrase but a different statement.
So yes, SQL injection is possible, but SQL injection cannot lead to data loss / corruption.
ooo... also see the pane to the right More like this: Is there any equivalent to SQL Injection in ABL?
Message was edited by: Stefan Drissen
Thanks Stefan,
I think therefore, we are relatively safe - all queries are not
query-prepare, but specific
MyNum = int(get-field("foo")) NO-ERROR.
/* check for errors */
/* checks to ensure that user has access to customer MyNum.
FOR EACH customer WHERE customer.Cust-num EQ MyNum NO-LOCK
etc etc
Thanks for the sanity check, though
Julian
Data corruption may be possible. Not in a single statement but consider this scenario:
A user can enter a login and password. As login he enters whatever and as password he enters wrongpassword" or login = "admin and the code evaluates this using a query-prepare or dynamic find-first:
buftheuser:FIND-FIRST( 'WHERE login = "' + icLogin + '" AND password = "' + icPassword + '"').
This query will evaluate to
WHERE login = "WHATEVER" AND password = "wrongpassword" or login = "admin"
In that case the user returned by the query will be admin, so the user will be logged in as admin. You would expect an admin user to be able to do pretty much whatever he wants, including corrupting data.
However, as far as I know this is not possible with static queries, so in the case of Julian you're safe.
Obviously the code above should not be in production regardless of the injection problem, the proper thing to do is not embed quotes in the query but use quoter() around the variables.
Oooo... nice.
But yes, as stated below (or was it above) anyone using dynamic queries without the QUOTER function is asking for it...
See yesterday's PANS Is ABL code injection preventable?
The ~042 (octal code for double quote) is not trapped by the QUOTER function.
Simply entering the following in an input field that is used in a query:
~042 OR TRUE AND ~042~042 = ~042
will trick the quoter resulting in ALL results being returned.
I consider this an urgent bug in the QUOTER function which should be fixed ASAP.
Curious. Did you report this to tech support?
No, I added additional sanitization to our query engine immediately.
When publishing a knowledge base article about SQL injection I am going on the assumption that a bug is logged. On the other hand, your reply is the first in this thread.
Hello,
that knowledge base entry was created after we logged a case with tech support after we discovered this issue (or at least we logged a case with tech support after we discovered this and they provided us with that article a couple of months ago.) The response from tech support was that this was not a bug because quoter is only intended to escape quotes, not ~. Basically you need to take care of this yourself.
Regards,
Jan
OK, so we should file a feature request :-)
With the renewed focus on OE in combination with the desire to make it a Cloud platform this becomes more important.
On the other hand, it's not so difficult to make it yourself indeed.
CLASS StringUtil:
METHOD PUBLIC STATIC CHARACTER Quoter(cIn AS CHARACTER):
ASSIGN
cIn = REPLACE(cIn, "~~", "~~~~")
cIn = REPLACE(cIn, "/*", "")
cIn = REPLACE(cIn, "*/", "")
.
RETURN QUOTER(cIn).
END METHOD. /* Quoter */
END CLASS.
Off-topic: I miss the publishing date in the knowledge base. It's never clear if I'm looking at recent or very old information.