I don't think I'm able to support '%' signs in the type because 'foo' encrypted will not be a substring contained within 'foobar' encrypted. Doing the translation by character would be too costly for introducing load on our encryption service.
The actual encryption/decryption is handled by hitting a remote RESTful service so I'm not sure I could do that on the DB side as you mention. This functionality isn't mission critical so I may just have to remove it from my application. Thanks for your input though! On Mar 29, 7:55 am, Michael Bayer <[email protected]> wrote: > 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 [email protected]. 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.
