SQL REGEXP
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 ! 
0
Best Answer
-
R_J
Admin
You may have luck with
->Where('u.Name regexp "^Clément([0-9]+)$"', null, false, false).2
Answers
Using REGEXP on this way is redundant
use
->Like('u.Name','Clément%').
sorry
->Like('u.Name','Clément','right')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
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 :![:\](https://open.vanillaforums.com/plugins/emojiextender/emoji/little/confused.png ":\")
where u.Name REGEXP "^Clément([0-9]+)$" is nullI'll drop $SQL->Where() and use $SQL->Query()
You may have luck with
->Where('u.Name regexp "^Clément([0-9]+)$"', null, false, false).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');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');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.
Thank you very much, it's perfect !