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.
