On Tuesday, April 19, 2016 at 6:53:16 AM UTC-7, Philipp Preß wrote:
>
> Hi,
>
> I have trouble setting up a MATCH / AGAINST query for MySQL in Sequel.
>
> MySQL's full text search features a boolean mode where, depending on the
> enclosing quotes of the search word, the query behaves differently.
>
> Example:
>
> SELECT * FROM `albums` WHERE MATCH(title) AGAINST(+"foobar" IN BOOLEAN
> MODE)
>
> SELECT * FROM `albums` WHERE MATCH(title) AGAINST(+'foobar' IN BOOLEAN
> MODE)
>
> Enclosing foobar in double quotes / single quotes is the difference here
> and triggers different matching behavior.
> I'm struggling to build the latter query where the search word is enclosed
> in double quotes when using Sequel's lit & parameters.
>
> Album.where(Sequel.lit("MATCH(title) AGAINST(+? IN BOOLEAN MODE)",
> "foobar")).sql
> #=> SELECT * FROM `albums` WHERE MATCH(title) AGAINST(+'foobar' IN BOOLEAN
> MODE)
>
> The provided parameter gets enclosed by single quotes, which causes the
> wrong behavior for my use case. Is it possible to build the query above
> with double quotes and using Sequel's parameterization feature or do I have
> to fall back to a raw SQL string in this case? If raw SQL is the only
> possibility here, can I still protect myself against SQL injections when
> the search word is coming from user input?
>
> I'm running this on:
> * sequel gem 4.13.0
> * mysql2 gem 0.3.20
> * MySQL 5.7.12
>
> Thanks in advance!
>
You'll probably need to escape the input manually, try this:
DB[:albums].full_text_search(:title, Sequel.lit("+\"#{str.gsub('"',
'\\"')}\""), :boolean=>true)
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
