[sqlalchemy] Joins with 3 tables to order results?
I've got a 'system' table which has a foreign key client_id to a 'client' table, and that has a FK vendor_id to a 'vendor' table. Currently I query and order by client_id and system attribute like:: systems = self.session.query(System).select(or_(System.c.lastseen == None, System.c.lastseen lately), order_by=[System.c.client_id,System.c.lastseen]) then using SQLAlchemy magic I can render the system.client.vendor.name and system.client.name. I want to do a query on 'system', get and order by vendor.name and client.name in that order. But I don't know how to construct a join so I can order this way. Any clues? Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] [Solved] Avoiding SQL injection with raw-ish queries (MySQL FULLTEXT search)
I finally discovered the Using Bind Parameters in Text Blocks section of the SQLAlchemy manual -- very useful and very easy to use. Perhaps this will help others who are trying to search against MySQL's FULLTEXT index safely. FWIW, I'm doing this in Pylons. Here's what I ended up doing: t = metadata.engine.text( SELECT ROUND(MATCH(message) AGAINST(:message), 2) AS score, facility,severity,message,explanation,solution,significance,os FROM kb WHERE MATCH(message) AGAINST(:message) AND facility=:facility AND severity=:severity LIMIT :limit ) c.results = t.execute(message=text, facility=fac, severity=sev, limit=100).fetchall() If you echo the SQL it's using, you can see how it quotes any query parameters that have quotes in them. Slick. Thanks for such a nice tool! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Avoiding SQL injection with raw-ish queries (MySQL FULLTEXT search)
I'm doing a query against a MySQL table that has a column which has
a fulltext index, so I need to do some raw-ish queries. Problem is
that these open me up to SQL injection attacks. How do I avoid them --
bound variables? filtering of quotes and funny chars?
I create the index on a table already created with SQLAlchemy by:
metadata.engine.execute(ALTER TABLE kb ADD FULLTEXT(message))
I later query against it with two exact matches and a MATCH...AGAINST
the fulltext index:
query =
SELECT MATCH(message) AGAINST('%s') AS score,
facility,severity,message
FROM kb
WHERE MATCH(message) AGAINST('%s')
AND facility='%s'
AND severity='%s'
LIMIT %s
query = query % (text,text,fac,sev,100)
results = metadata.engine.execute(query).fetchall()
This works nicely most of the time.
If one of the parameters includes a single-quote, however, it breaks
the query. This seems a avenue for an SQL injection attack.
Problem is I can't figure out how to use SQLAlchemy's bound variables
to re-implement the query.
I can remove the SELECT part of the query string and do something like:
query =
MATCH(message) AGAINST('%s') AS score,
facility,severity,message
FROM kb
WHERE MATCH(message) AGAINST('%s')
AND severity='%s'
query = query % (text,text,sev)
results = select([query], engine=metadata.engine).execute().fetchall()
But that doesn't help with the SQL injection.
If I remove the AND portion from the query string and move it into the
execute():
results = select([query],
engine=metadata.engine).execute(severity=sev).fetchall()
it doesn't help at all -- the SQL that's echoed indicates no
AND severity... at all and I get too many results.
Is there a way I can do the MATCH...AGAINST but using bound variables,
or some other way that SQLAlchemy can protect me from injection
attacks?
If not, how do you recommend I sanitize the user-supplied query
parameters so the query can't be exploited?
Thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
I'd be interested in how you work this out as I want to do something similar. Would you be willing to write it up and perhaps post it to the wiki? I was able to get MySQL's fulltext search working more quickly than PostgreSQL's and that's what my customer is used to so that's what I'm going to use for them. I haven't yet gotten to integrate it with SA. Down the road, I'd like to know how to do the same with PG. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] How to query like sys_id=42 AND ts_created 90 minutes ago ?
I've got a bunch of history and other timestamped information I will
need to query against. The columns are created with type DateTime and
get set upon row creation:
history_table = Table(
'history', metadata,
Column('history_id', Integer,primary_key=True),
Column('system_id', Integer,ForeignKey('system.system_id'),
nullable=False),
Column('ts_created', DateTime,
default=func.current_timestamp()),
Column('ts_updated', DateTime,
onupdate=func.current_timestamp()),
)
I'm going to want to do lots of queries on this 'history' table for a
specific 'system_id' and a 'ts_created' within some duration in the
past -- like 5 or 60 minutes.
It's taken me a while to figure out the SQLAlchemy syntax to make this
work and it seems a bit verbose:
session.query(History).select(and_(History.c.system_id==42,
History.c.ts_created
datetime.datetime.now() - datetime.timedelta(minutes=90)))
Is there a better, more concise way to say this?
Thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Additions to model not getting flushed to DB?? (Pylons)
I'm doing a Pylons app and must be doing something stupid because
similar logic seems to work elsewhere, but this just
isn't sticking in the DB. I query to get existing 'contacts',
they're empty so I create a new one, append it, save and flush, but it
doesn't show up in the diagnostic queries I do. Perhaps I'm not
seeing something obvious, any suggestions?
contacts =
self.session.query(model.Contact).select_by(client_id=client.client_id)
print #vendor.upload DBQ contacts=, contacts
contacts = client.contacts
print #vendor.upload DOT contacts=, contacts
contact = model.Contact(client_id=client.client_id,
email=row['contactemail'],
password=row.get('contactpassword'),
firstname=row.get('contactfirstname'),
lastname=row.get('contactlastname'),
)
print #vendor.upload New contact=, contact
print #vendor.upload Query contacts before append=,
self.session.query(model.Contact).select()
contacts.append(contact)
print #vendor.upload Query contacts after append=,
self.session.query(model.Contact).select()
self.session.save(contact)
print #vendor.upload Query contacts aftersave=,
self.session.query(model.Contact).select()
self.session.flush()
print #vendor.upload Query contacts after flush=,
self.session.query(model.Contact).select()
When I run it, you see it creates a new one but then none of the
queries find the contact from a query. An SQL query against the MySQL
DB show's it's not their either.
#vendor.upload DBQ contacts= []
#vendor.upload DOT contacts= []
#vendor.upload New contact= Contact(None,9,[EMAIL PROTECTED],None,None,None
[None, None])
#vendor.upload Query contacts before save= []
#vendor.upload Query contacts after save= []
#vendor.upload Query contacts after flush= []
The client and contact model and relations look like:
client_table = Table('client', metadata,
Column('client_id',Integer,
primary_key=True),
Column('vendor_id',Integer,
ForeignKey('vendor.vendor_id'), nullable=False),
Column('name', String(40), unique=True,
nullable=False),
Column('ts_created', DateTime,
default=func.current_timestamp()),
Column('ts_updated', DateTime,
onupdate=func.current_timestamp()),
)
mapper(Client, client_table,
properties={'contacts': relation(Contact, lazy=False),
'systems': relation(System, lazy=False),
})
contact_table = Table('contact', metadata,
Column('contact_id',Integer,
primary_key=True),
Column('client_id', Integer,
ForeignKey('client.client_id'), nullable=False),
Column('email', String(80),
unique=True, nullable=False),
Column('password', String(40),
nullable=False),
Column('firstname', String(40),
nullable=True),
Column('lastname', String(40),
nullable=True),
Column('ts_created', DateTime,
default=func.current_timestamp()),
Column('ts_updated', DateTime,
onupdate=func.current_timestamp()),
)
Any ideas? Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Additions to model not getting flushed to DB?? (Pylons)
Doh, I should have turned on the SQL echo. Now that I have, it's even
more puzzling to me. It shows the 'contact' being inserted and
committed -- but it never appears in MySQL!
2006-12-23 17:33:15,140 INFO sqlalchemy.engine.base.Engine.0x..cc BEGIN
2006-12-23 17:33:15,145 INFO sqlalchemy.engine.base.Engine.0x..cc SELECT
current_timestamp
2006-12-23 17:33:15,145 INFO sqlalchemy.engine.base.Engine.0x..cc []
2006-12-23 17:33:15,150 INFO sqlalchemy.engine.base.Engine.0x..cc INSERT INTO
contact (client_id, email, password, firstname, lastname, ts_created,
ts_updated) VALUES (%s, %s, %s, %s, %s, %s, %s)
2006-12-23 17:33:15,150 INFO sqlalchemy.engine.base.Engine.0x..cc [9L, '[EMAIL
PROTECTED]', None, None, None, datetime.datetime(2006, 12, 23, 17, 33, 44),
None]
2006-12-23 17:33:15,158 INFO sqlalchemy.engine.base.Engine.0x..cc COMMIT
#vendor.upload Query contacts after flush=[]
If I switch my Pylons development.ini to use an SQLite DB, the app
works as expected until I get to the same flush():
contact = model.Contact(client_id=client.client_id,
email=row['contactemail'],
password=row.get('contactpassword'),
firstname=row.get('contactfirstname'),
lastname=row.get('contactlastname'))
self.session.save(contact)
self.session.flush()
The Pylons traceback reports:
self.session.flush()
...
sqlalchemy.exceptions.SQLError: (OperationalError) SQL logic error or missing database 'INSERT INTO contact (client_id, email, password, firstname, lastname, ts_created, ts_updated) VALUES (?, ?, ?, ?, ?, ?, ?)' [1, '[EMAIL PROTECTED]', None, None, None, '2006-12-24 00:25:35', None]
The SQL echo looks good, until the ROLLBACK:
2006-12-23 19:25:35,874 INFO sqlalchemy.engine.base.Engine.0x..0c BEGIN
2006-12-23 19:25:35,878 INFO sqlalchemy.engine.base.Engine.0x..0c SELECT
current_timestamp
2006-12-23 19:25:35,878 INFO sqlalchemy.engine.base.Engine.0x..0c []
2006-12-23 19:25:35,879 INFO sqlalchemy.engine.base.Engine.0x..0c INSERT
INTO contact (client_id, email, password, firstname, lastname, ts_created,
ts_updated) VALUES (?, ?, ?, ?, ?, ?, ?)
2006-12-23 19:25:36,031 INFO sqlalchemy.engine.base.Engine.0x..0c [1,
'[EMAIL PROTECTED]', None, None, None, '2006-12-24 00:25:35', None]
2006-12-23 19:25:36,086 INFO sqlalchemy.engine.base.Engine.0x..0c ROLLBACK
That self.session is created with a Pylons __before__ method:
def __before__(self):
self.session = session_context.current
self.q = self.session.query(model.Vendor)
and other methods use this just fine as far as I can tell.
How can I track down whether there's a missing database or what the
SQL logic error might be?
Thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Additions to model not getting flushed to DB?? (Pylons)
Michael Bayer [EMAIL PROTECTED] writes: seems like Pylons is not properly maintaining the session within requests. Yeah, it sure feels like that. :-( unless there is some way you can reproduce this problem without any dependencies on pylons ? is this using the newest SA/Pylons code that Ben was recently working on ? Hrmmm, I'm not sure ... I am not on the bleeding edge with nightly easy_install -U *=dev :-) I'll see if I can reproduce in a small scale in Pylons; if so, I'll try reproducing in standalone SA. Thanks for the pointers. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to auto-set timestamp: MySQL func.now(), but not SQLite
Michael Bayer [EMAIL PROTECTED] writes: func.current_timestamp() (which evaulates without the parenthesis in SQL) Excellent, works like a charm. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
