SQL REGEXP

Clément

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 !

R_J

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

x00

Using REGEXP on this way is redundant

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

peregrine

.

x00

sorry

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

JasonBarnabe

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

Clément

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()

R_J

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

Clément

It works !!!

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');

x00

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');

x00

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.

Clément

Thank you very much, it's perfect !