Blog Documentation Try Vanilla Cloud
Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Options

SQL REGEXP

ClémentClément ✭
edited December 2014 in Vanilla 2.0 - 2.8

Hello,

I can not build a SQL query with REGEXP.

I tested with :

    $SQL = Gdn::SQL();
        $SQL
            ->Select('u.Name')
            ->From('User u')
            ->Where('u.Name REGEXP', '^Clément')
            ->Limit(1)
            ->OrderBy('u.Name', 'desc');

Or :

        $SQL = Gdn::SQL();
        $SQL
            ->Select('u.Name')
            ->From('User u')
            ->Where('u.Name REGEXP "^Clément"')
            ->Limit(1)
            ->OrderBy('u.Name', 'desc');

Thank you for your help ! :)

Best Answer

  • Options
    R_JR_J Ex-Fanboy Munich Admin
    edited December 2014 Answer ✓

    You may have luck with ->Where('u.Name regexp "^Clément([0-9]+)$"', null, false, false).

Answers

  • Options
    x00x00 MVP

    Using REGEXP on this way is redundant

    use ->Like('u.Name','Clément%')

    grep is your friend.

  • Options
    peregrineperegrine MVP
    edited December 2014

    .

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • Options
    x00x00 MVP

    sorry

    ->Like('u.Name','Clément','right')

    grep is your friend.

  • Options
    JasonBarnabeJasonBarnabe Cynical Salamander ✭✭

    Assuming you need REGEXP for something more important than that example, what happens if you use what you have? You may be getting hit by https://github.com/vanilla/vanilla/pull/2308

  • Options
    ClémentClément ✭
    edited December 2014

    Thank you x00. It was just one example, the final sql will be more complex, like:
    ->Where('u.Name REGEXP "^Clément([0-9]+)$"')

    Result : where u.Name REGEXP "^Clément([0-9]+)$" is null :\

    I'll drop $SQL->Where() and use $SQL->Query()

  • Options
    R_JR_J Ex-Fanboy Munich Admin
    edited December 2014 Answer ✓

    You may have luck with ->Where('u.Name regexp "^Clément([0-9]+)$"', null, false, false).

  • Options
    ClémentClément ✭
    edited December 2014

    It works !!! :D

    Last question : How to avoid sql injection with variable $Name ?

    Exemple :

                $Name = 'Clément';
            $SQL = Gdn::SQL();
            $SQL
                ->Select('u.Name')
                ->From('User u')
                ->Where('u.Name REGEXP "^'.$Name.'([0-9]+)$"', null, false, false)
                ->Limit(1)
                ->OrderBy('u.Name', 'desc');
  • Options
    x00x00 MVP
    edited December 2014

    clement I was thinking the same. escape like so:

                $Name = 'Clément';

            $Name = preg_quote($Name);

            $SQL = Gdn::SQL();
            $SQL->NamedParameter('RegexUserName', TRUE, "^{$Name}([0-9]+)$" );

            $SQL = Gdn::SQL();
            $SQL
                ->Select('u.Name')
                ->From('User u')
                ->Where('u.Name REGEXP :RegexUserName', null, false, false)
                ->Limit(1)
                ->OrderBy('u.Name', 'desc');

    grep is your friend.

  • Options
    x00x00 MVP

    I provided two types of escape. The regular expression and the query quote.

    You might actually want to simply use the garden user name regular expression, to check it against that.

    grep is your friend.

  • Options
    ClémentClément ✭

    Thank you very much, it's perfect ! :)

Sign In or Register to comment.