[jifty-devel] jifty-dbi 0.71: Unknown operator 'REGEXP'

[Stanislav Sinyagin]

hi,

The following construct worked fine (with MySQL backend) with an old version (0.68 or so), and stopped working with jifty-dbi 0.71:
     $devices->limit(column => 'hostname',
                            operator => 'REGEXP',
                            value => $re,
                            entry_aggregator => 'OR');

Obviously line 1284 in lib/Jifty/DBI/Collection.pm produces that.

It will be great to have a workaround which allows non-ANSI SQL operators.



regards,
stanislav

[Thomas Sibley]

Post by Stanislav Sinyagin
Obviously line 1284 in lib/Jifty/DBI/Collection.pm produces that.
It will be great to have a workaround which allows non-ANSI SQL operators.

Perhaps a different key? Or a refactoring the operator check into the
handle class, so it can be db-specific?

I'm not sure what the best solution is at the moment.

Thomas

[Ruslan Zakirov]

Post by Stanislav Sinyagin
Obviously line 1284 in lib/Jifty/DBI/Collection.pm produces that.
It will be great to have a workaround which allows non-ANSI SQL operators.

Perhaps a different key?  Or a refactoring the operator check into the
handle class, so it can be db-specific?
I'm not sure what the best solution is at the moment.

That check was implemented to prevent SQL injections and It's possible
to loose granularity to:

/^(=|<|>|!=|<>|<=|>=|[a-z_\s]+)$/ix

Or a little bit more precise:

/^(=|<|>|!=|<>|<=|>=|((IS\s+)?(NOT\s+)?)[a-z_]+)$/ix


I think it covers security pretty well and gives freedom.

Thomas
_______________________________________________
jifty-devel mailing list
http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel

[Stanislav Sinyagin]

would be great to have that on Github...

________________________________
Sent: Tuesday, August 30, 2011 7:09 PM
Subject: Re: [jifty-devel] jifty-dbi 0.71: Unknown operator 'REGEXP'

Post by Stanislav Sinyagin
Obviously line 1284 in lib/Jifty/DBI/Collection.pm produces that.
It will be great to have a workaround which allows non-ANSI SQL operators.

Perhaps a different key?  Or a refactoring the operator check into the
handle class, so it can be db-specific?
I'm not sure what the best solution is at the moment.

That check was implemented to prevent SQL injections and It's possible
/^(=|<|>|!=|<>|<=|>=|[a-z_\s]+)$/ix
/^(=|<|>|!=|<>|<=|>=|((IS\s+)?(NOT\s+)?)[a-z_]+)$/ix
I think it covers security pretty well and gives freedom.

Thomas
_______________________________________________
jifty-devel mailing list
http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel

--
Best regards, Ruslan.
_______________________________________________
jifty-devel mailing list
http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel

[Ruslan Zakirov]

If more developers +1 this then I don't see problems pushing it into
the repo. You can always cook a pull request.

Post by Stanislav Sinyagin
would be great to have that on Github...
________________________________
Sent: Tuesday, August 30, 2011 7:09 PM
Subject: Re: [jifty-devel] jifty-dbi 0.71: Unknown operator 'REGEXP'

Post by Stanislav Sinyagin
Obviously line 1284 in lib/Jifty/DBI/Collection.pm produces that.
It will be great to have a workaround which allows non-ANSI SQL operators.

Perhaps a different key?  Or a refactoring the operator check into the
handle class, so it can be db-specific?
I'm not sure what the best solution is at the moment.

That check was implemented to prevent SQL injections and It's possible
/^(=|<|>|!=|<>|<=|>=|[a-z_\s]+)$/ix
/^(=|<|>|!=|<>|<=|>=|((IS\s+)?(NOT\s+)?)[a-z_]+)$/ix
I think it covers security pretty well and gives freedom.

Thomas
_______________________________________________
jifty-devel mailing list
http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel

--
Best regards, Ruslan.
_______________________________________________
jifty-devel mailing list
http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
_______________________________________________
jifty-devel mailing list
http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel