[mod_python] Problem with html quoted/unquoted

Wouter van Marle wrote:
> On Wed, 2006-05-17 at 11:46 -0400, Jim Gallacher wrote:
>> Wouter van Marle wrote:
>>> Dear Deron,
>>>
>>> Thank you for the comments. I understand your ideas; unfortunately it
>>> does not solve my problem.
>>>
>>> The info I get from another website, this origin gives me the info in
>>> the ampersand form (that third party site is a Netscape server by the
>>> way! Didn't know they are still in use, very remarkable). And I like
>>> that.
>>> The main reason to continue using that format is the " (double-quotes)
>>> and ' (single quotes). These characters are used in the data that I try
>>> to store in the mysql base, and that fantastically messes up with the
>>> queries....
>>> imagine: s = "this is 'a' string"
>>> then say query = """ SELECT * FROM base WHERE field = "%s";"""% s
>>>
>>> But what about when s can be 'this is "a" string'
>>> or s = """this is a 5", 'b' sized thing""".
>>
>> Don't build your query string that way, *especially* if you are getting 
>> data from an untrusted source. You are laying yourself open to a SQL 
>> injection attack. You want to use parameter passing. (for MySQLdb)
>>
>> cur.execute("SELECT * FROM base WHERE field = %s", s)
> 
> I have tried this (once) with MySQLdb, and it doesn't seem to work like
> that. (v1.2.1g2) Didn't pursue the issue further.

You must be doing something wrong. A quick test on my system:

 >>> s = """This is 'some' stuff with "quotes" and stuff"""
 >>> cursor.execute('insert into testtable (data) values (%s)', s)
1L

mysql.log output
----------------

060517 17:27:39	     75 Query       insert into testtable (data) values 
('This is \'some\' stuff with \"quotes\" and stuff')

This really is the correct way to pass parameters to your query.

> I am aware of the issues of SQL injection. For that reason I've given
> the web script a special user with only very limited capabilities, to at
> least make it a little harder for attackers.

Don't make it hard, make it impossible. Bugtraq has reports of SQL 
injection attacks in PHP applications almost every day. This sort of 
problem can be avoided in python by using the appropriate mechanism for 
executing SQL queries, so do yourself a favour and get in the habit of 
doing it the right way. DB access should be restricted to the bare 
minimum required for your app to work anyway, but should not be a 
substitute for writing a secure application in the first place. Your 
requirements may change in the future which necessitate relaxing the db 
restrictions, and then you are faced with auditing your code to correct 
the deficiencies, or even worse you'll have completely forgotten about 
the security issue.

I hope I'm not sounding too harsh here Wouter, as that is not my 
intention. I'm just trying to help.

Jim