On Mar 29, 2011, at 4:12 AM, Stephen wrote: > Hello, > > I'm using a custom type (TypeDecorator) to handle encrypting data on > write and decrypting data on read. Everything works fine for inserting > and reading from the database with one exception. Using the like > operator with '%' syntax is not returning any results. > > For the purposes of this post, lets suppose the data manipulation is > simple base 64 encoding: > #code block: > class EncryptedString(TypeDecorator): > > impl = String > > def process_bind_param(self, value, dialect): > if not value: > return value > > return base64.b64encode(value) > > def process_result_value(self, value, dialect): > if not value: > return value > > decrypted_sub_string = base64.b64decode(value) > > #and a simple model: > Base = declarative.declarative_base() > class MyTable(Base): > __tablename__ = "mytable" > > name = EncryptedString(128) > > #and the query that does not work: > Session().query(MyTable).filter(MyTable.name.like('%foo%') > #end code block > > Let's say there's a row in the table with name = 'foobar' (actually > its whatever 'foobar' encodes to) the above query will return nothing. > If I query for .like('foobar') then I will get the row returned as > expected. > > Lastly, I'm on v0.5.1 (way behind I know!). > > Any thoughts? Thanks so much,
It would appear that your encrypted type is also encrypting the % signs. It depends highly on the style of encryption you are using what path to take here. If the translation is one-to-one on a per character basis then you might want to do a comparison "column LIKE '%' + 'encrypted' + '%', which would involve working around MyTable.name's forced coercion of the right hand side, probably using col.like(literal('%', type_=String) + my_encryption('foo') + literal('%', type_=String)). Or modifying your type to allow '%' signs through, perhaps if they are escaped in some special way. The more robust method is to allow the % operator to work naturally by performing the decryption on the database side: func.decrypt(MyTable.name).like('%foo%') where "decrypt" would be a function or custom procedure on the DB side that does the same decryption. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.