WP-A: A New URL Shortener

Fri Mar 25 23:30:11 EDT 2016

Chris Angelico wrote:

> On Sat, Mar 26, 2016 at 9:25 AM, Thomas 'PointedEars' Lahn
> <PointedEars at web.de> wrote:

Attribution line, not attribution novel.

>> Chris Angelico wrote:
>>> On Sat, Mar 26, 2016 at 8:28 AM, Thomas 'PointedEars' Lahn
>>> <PointedEars at web.de> wrote:
>>>> Then enlighten me, please: How is “parameterization” or a
>>>> “parameterized query”, as *you* understand it, different from a
>>>> prepared statement?
>>>
>>> This is a prepared statement:
>>>
>>> http://www.postgresql.org/docs/current/static/sql-prepare.html
>>>
>>> You use a special "PREPARE" query to create *and store* a half-run
>>> query,
>>
>> [Having written a database layer (in PHP) myself, I know what a prepared
>> statement is, thank you very much.]
>>
>> Your statement is incorrect both for MySQL (which I know) and PostgreSQL
>> (AIUI your reference): The query is processed and stored of course, but
>> it is definitely _not_ *run* before the EXECUTE command is issued for it.
> 
> It legal to partly perform the query.

What are you getting at?  A query cannot be performed if the values of its 
parameters have no defined value yet.

> DB2 can retain a "query plan" which consists of the exact paths it will
> follow. So I stand by my statement.

A query plan is _not_ the *execution* (“run”) of a query, but the result of 
*processing* a query (AISB).  So you are still wrong.

>>> Back in the 1990s, I had the option of actually *compiling* my SQL
>>> queries as part of my C code, which would prepare all the queries for
>>> future execution. It is completely different from the dynamic
>>> parameterized queries that most people use.
>> Interesting, but irrelevant.
> 
> Not really, no. It was a form of PREPARE/EXECUTE that hid the PREPARE
> behind a convenient syntax, and then replaced it with EXECUTE for the
> run-time.

IOW, a prepared statement.  Thanks for the confirmation.

>>> Parameterization is a more general concept which prepared statements
>>> invariably use, but which general code need not use.
>>
>> If it is to be safe from SQL injection, it better uses a parameterized
>> query as it is *commonly* understood.
> 
> Why? […]

Far beyond the scope of this newsgroup/mailing list.  RTFM I referred to.

>>> A Python database connector could choose to PREPARE/EXECUTE for every
>>> query it's given,
>> That would not be wise, given that not every query contains variable
>> parameters.
> 
> Irrelevant. It would still be legal. Do you understand the difference
> between what is legal according to a specification and what is
> actually worth doing?

Red herring.  Legality (better: validity) was not the issue.  Whether 
legal/valid code or or not, it would still not be wise because application 
performance would be reduced at little advantage, if any.

>>> or it could choose to escape all the parameters and embed them,
>> Recommended against, and not a parameterized query at all.
> 
> Not once it reaches the underlying database,

And that is not how “parameterized query” is commonly defined.

>>> All three are "parameterized queries", at least from the POV of Python
>>> code.
>> You are mistaken, then: Of the three kinds of “parameterized queries” as
>> *you* understand them (OWASP and I beg to differ), at least one of them
>> that is not used in a prepared statement is *insufficient to prevent SQL
>> injection*.
> 
> Okay, then. Please explain how this code is vulnerable to SQL injection:
> 
> conn = some_db_module.connect("")
> cur = conn.cursor()
> data = input("Enter a value: ")
         ^^^^^
> cur.execute("insert into some_table (some_column) values (%s)", (input,))
                                                                   ^^^^^
> conn.commit()

This code is not vulnerable to SQL injection, unless “input” referred to an 
object that had both a suitable __call__() or __init__(), and __str__() 
method ;->

Probably you meant

#---------------------------------------------------------------------------
conn = some_db_module.connect("")
cur = conn.cursor()
data = input("Enter a value: ")
cur.execute("insert into some_table (some_column) values (%s)", (data,))
conn.commit()
#---------------------------------------------------------------------------

Since nothing indicates the used module and accessed DBMS (only that, if it 
is Python code, the module cannot be sqlite3 as that does not support “%s”), 
then this code can, if the module uses an escaping mechanism, still be 
vulnerable to SQL injection.  For example, I could input something to the 
effect of

#---------------------------------------------------------------------------
data = r'\"); DROP TABLE some_table; --'
#---------------------------------------------------------------------------

if, for example, the string escaping mechanism in the module would simply 
duplicate any double-quote it finds to escape it in the string literal that 
it created (as is possible in MySQL and PostgreSQL), and still inject my 
code because the resulting query would be

  insert into some_table (some_column) values ("\"");
  DROP TABLE some_table;
  --")

which is at least syntactically valid MySQL code, but from the perspective 
of the so-attacked it is still not fine as the table would be gone 
afterwards.