[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 -~--~~~~--~~--~--~---