SQL Injection Attacks

[horridohobbyist]

Does web2py have a function or means of "sanitizing" user input in order to prevent SQL injection attacks?

Thanks.

[Niphlod]

if you pass "raw variables" to DAL's queries, the input is sanitized.

If you instead try to build a query doing cut/paste, e.g.

myquery = "select * from table where field = %s" % raw_variable

then it's not sanitized.

[Cliff Kachinske]

http://web2py.com/books/default/chapter/29/01/introduction#Security

[Ovidio Marinho]

http://www.pythonsecurity.org/wiki/web2py/

      

         Ovidio Marinho Falcao Neto

                  ITJP.NET.BR                     
             ovid...@gmail.com

             itjp....@gmail.com

                         Brasil

              

2014-02-02 Cliff Kachinske <cjk...@gmail.com>:

--
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 web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[horridohobbyist]

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.

[Massimo Di Pierro]

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