SQL is a serialized protocol. The application sends to the database one
single string no matter what API you use.
The only difference between this:
execute('SELECT * FROM Orders WHERE OrderId=%s' % escape(userdata))
and this
execute('SELECT * FROM Orders WHERE OrderId=?', userdata)
is whether the escaping is done explicitly or inside the execute function
provided by the driver. The same string is sent to the database.
What matters is that web2py never requires the developer to do escape(...)
explicitly because the queries are built programmatically. If one uses the
DAL to build queries SQL injections are impossible.
There are two exception. Exception 1) Old versions of postgresql do not
handle escaping in a way conform to the SQL standard. For this reason
web2py on postgresql web2py sets standard_conforming_strings=on; thus
restoring the conforming behavior. Exception 2) when available we use the
escape function provided by the driver. If the driver has a bug we have a
vulnerability (notice the ? notation would use the very same buggy escape
function internally).
Because of caveats like the ones above I trust the way web2py handles
escaping better than is it were hidden in the drive (the driver may not
know my database settings when handling conforming or non-conforming string
escaping). Although I agree that the '?' notation is more aesthetically
pleasing and probably we should use it in the future (but not because it
adds any security to web2py).
Massimo
On Monday, 3 February 2014 19:54:13 UTC-6, horridohobbyist wrote:
>
> I understand what everyone is saying. However, a friend of mine still
> questions whether web2py's security model is absolutely rock-solid. Even
> though SQL injection is "impossible" in web2py, there may still be a way to
> circumvent the security. For example, he says...
>
> Let me illustrate with pseudocode, where I will denote a variable
> containing user input as $userdata. What you want to avoid is seeing
> stuff in the framework like
>
> send_sql ('SELECT * FROM Orders WHERE OrderId=$userdata')
>
> Here, $userdata is added before conversion to bytecode to be handed down
> to the DB for processing. You can try to escape it as much as you want,
> but somebody is gonna craft a string to spoof it. Instead, you want to
> look for something like
>
> $stmt = prepare_sql ('SELECT * FROM Orders WHERE OrderId=?')
> send_sql ($stmt, $userdata)
>
> Here, the query is converted to bytecode *before* the addition of user
> input, thus making it immune to this form of injection attack.
>
>
> He seems to make a good point. Can anyone confirm how web2py is handling
> this situation?
>
> Thanks.
>
> On Sunday, 2 February 2014 17:28:47 UTC-5, Cliff Kachinske wrote:
>>
>> http://web2py.com/books/default/chapter/29/01/introduction#Security
>>
>>
>> On Sunday, February 2, 2014 11:33:02 AM UTC-5, horridohobbyist wrote:
>>>
>>> Does web2py have a function or means of "sanitizing" user input in order
>>> to prevent SQL injection attacks?
>>>
>>> Thanks.
>>>
>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
