[BangPypers] [ANN][X-Post] SciPy India conference in Dec. 2009

[Carl Karsten]

On Sat, Oct 10, 2009 at 9:03 AM, Noufal Ibrahim <noufal at gmail.com> wrote:
> On Sat, Oct 10, 2009 at 12:31 PM, Anand Balachandran Pillai
> <abpillai at gmail.com> wrote:
> [..]
>>    For example, this is a very common way of doing a select using PHP.
>>
>>   $query = "SELECT * FROM products WHERE name=’$productname’";
>>    mysql_query($query);
>>
>>   Only that this kind of SQL is very vulnerable to SQL injection attacks
>> because
>>   $productname can be replaced with malicious SQL code from outside.
>>
>>   The correct way to do this would be,
>>
>>   $query = sprintf("SELECT * FROM products WHERE name=’%s’",
>>    mysql_real_escape_string($productname));
>>    mysql_query($query);
>>
>>  However, in Python due to some features like multiline strings and
>>  templating using a dictionary, these kind of issues are more easily
>> avoided.
>>
>>  example
>>
>>  query="""SELECT * from Products WHERE name=%s AND timestamp>=%s"""
>>  cursor.execute(query % ('burger', '2009-09-10 12:00:00')
>
> I'm not sure I understand here. You're using  constants here ('burger'
> and '2009...') but variables in the PHP version. Since constants are
> in your own code, they're trusted data but since the variables could
> contain values from the outside, you're still vulnerable to SQL
> injection unless you sanitise them. If you had a variable (say foo)
> instead of 'burger' and that had some malicious SQL injection attempt,
> you'd be vulnerable exactly like in the PHP version wouldn't you? Are
> you saying you don't need to escape the variables in the Python
> version? I haven't played with the raw dbapi for a long time so I
> might be just spouting nonsense here.


I need to inject...

The line should be:

cursor.execute(query, ('burger', '2009-09-10 12:00:00'))

and to keep it more like the php example:
cursor.execute(query, (productname, buy_datetime))

and now to digress int sql server stuff:

Most (all?) sql servers have the concept of query parameters.  This
means you put "markers" in the query (like %s) and supply the values
separately.  to be clear: the server does not have to parse the values
out of the query, because they are never in the query.  This pretty
much removes the SQL Injection issue.   It also means the server can
cache stuff about queries better because the query doesn't change,
just the parameters.


>
>
>>  It is not easy to use SQL injection against code like above so the
>>  default Python string templating is a bit more secure than the one
>>  provided by PHP.  You don't need to go through the pain of
>> mysql_escape_string  to escape the SQL params which is the solution
>>  offered in the PHP world.
>
> I'm not sure I see the difference. Assuming you get two variables from
> the outside product and date which contain the query parameters, what
> would happen here?
> query="""SELECT * from Products WHERE name=%s AND timestamp>=%s"""
> product = "''; SHOW DATABASES;" # SQL Injection attempt
> date = '2009-09-10 12:00:00'
> print query%(product,date)
> SELECT * from Products WHERE name=''; SHOW DATABASES; AND
> timestamp>=2009-09-10 12:00:00
>
> which would get screwed no?
>
>>  This is just one example. Basically it is a fact that the clean, minimal
>>   syntax of Python with no "hackish" features does make it a more
>>  secure language, if not intentional then accidental. Anyway it is good
>>   news for Python developers.
>
> I think the language quality amplifies programmer skill. IF you have
> two entry level PHP and Python programmers, I'm willing to bet that
> they're code will be somewhat similar and language specific goodness
> will not be there. As they become skilled, their ability to exploit
> languages become better and the differences will be sharper. So, a
> mature product hacked upon by good programmers will be considerably
> better  if it's in a 'superior' language. I think Drupal and Plone
> come into that category so the security argument holds. However, for
> an average programmer doing a typical program, PHP vs. Python won't
> make *much* of a difference except decide her future.

I have done 2 php pages and a bunch of python.  My guess is PHP makes
it easier to write vulnerable code, but I am really going on on a limb
here, so I'll not try to support this notion.


-- 
Carl K