Camelot a good tool for me

[Chris Angelico]

On Sat, May 23, 2015 at 8:42 AM, Cameron Simpson <cs at zip.com.au> wrote:
> I like SQLAlchemy because:
>
>  - it quotes for me, avoiding an infinity of pain and injection risk
>
>  - it presents results as nice objects with attributes named after columns
>
>  - it lets me write SQL queries as nice parameterised Python syntax instead
> of clunky SQL
>
>  - it automatically hooks into various backends, which means I can write
> many  tests or prototypes using, for example, in-memory SQLite dummy
> databases  while still speaking to MySQL or PostgreSQL in the real world.
> (Obviously you  need to run some tests against the real thing, but looking
> for SQL logic  errors is readily tested against throwaway SQLite or the
> like.)

Interestingly, the Python DB API actually gives you three out of four of that.

It quotes for you:
cur.execute("insert into Students (name, year) values (%s, %s)",
("Robert'); DROP TABLE Students;--", 2012))

It automatically hooks into various backends:

import psycopg2
conn = psycopg2.connect("dbname=whatever user=me password=secret")

import mysqldb
conn = mysqldb.connect(user="me", password="secret", db="whatever")

import sqlite3
conn = sqlite3.connect("whatever.db")

After that, you can mostly use the same code everywhere. (The
differences will be because of inherent differences in the backends,
not issues with the API.)

You don't automatically get nice objects with attributes named after
columns, but neither will SQLAlchemy unless you're doing the
equivalent of "select * from". Example:

>>> session.query(Student).all()
[<__main__.Student object at 0x7fc2de3fc290>, <__main__.Student object
at 0x7fc2de3fc350>, <__main__.Student object at 0x7fc2de3fc3d0>]

That's a list of Student objects, because I asked for the entire table.

>>> session.query(Student.name, Student.year).all()
[(u'Fred', 2015), (u'Joe', 2015), (u"Robert'); DROP TABLE Students;--", 2012)]

That's a list of tuples, because I asked for just two columns. So what
you're really saying is that SQLAlchemy subtly suggests that you
should grab the entire table any time you need any information out of
it. This is bad for a couple of reasons: firstly, it defeats certain
optimizations (for instance, if you ask for an indexed column, the
database might not need to read from the base table at all), and
secondly, it opens up dangerous possibilities of excessive data
transfer. You start pickling these objects, or JSONifying them, or
whatever, and sending them to untrusted destinations, and voila,
you're giving away all your columns instead of just the few that
they're allowed to see. Yes, this isn't really a part of the
SQLAlchemy interface, but it's part of the same philosophy of "just
give me the object and let me do what I like with it". I've seen
commercial systems that have had this exact flaw, looking very much
like the programmers used too many of these fancy shortcuts and not
enough actual thinking about what they're doing.

So, yes, in the specific case where you want to take the entire table,
SQLAlchemy gives you something a little prettier. If you're
parsimonious, the two are the same.

The only one that PEP 249 doesn't give is your second point, that you
can use Python syntax to craft queries. Sure. That's SQLAlchemy's
schtick, and it's fine. But the other three of your points -
particularly the serious one about SQL injection prevention - are all
handled elsewhere too.

ChrisA