Mysql REGEX Matching query

Question

What is the correct query to match string variable with MySQL field? For example:

$var_surname = $_POST['surname'];
$var_surname = strtolower($var_surname);

SELECT surname FROM tblname WHERE LOWER(surname) REGEX $var_surname
..........
..........

if($check > 0)
{
    Surname already exists
} 
else 
{
    Successful
}

Any help will be appreciated.

I hope you're sanitizing $var_surname before inserting it into the SQL like that! — Justin Morgan
– Justin Morgan, Commented Feb 23, 2011 at 15:33
In fact, even if you are sanitizing it, running a user-supplied regex on your database server is asking for a DoS attack. It also means you have to sanitize it for SQL while not affecting the regex. Never simply trust what you get from $_POST, it's trivial to view and edit that from client-side. — Justin Morgan
– Justin Morgan, Commented Feb 23, 2011 at 15:40

Dalmas · Accepted Answer · 2011-02-23 15:31:25Z

1

I don't understand, why are you using REGEX (should be REGEXP by the way) ? Just do

"SELECT surname FROM tblname WHERE LOWER(surname) = '$var_surname'"

answered Feb 23, 2011 at 15:31

Dalmas

26.6k9 gold badges71 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Donut · Accepted Answer · 2011-02-23 15:35:22Z

1

REGEX isn't a valid MySQL operator, it's REGEXP:

SELECT surname FROM tblname WHERE LOWER(surname) REGEXP '$var_surname';

However, REGEXP looks like it's overkill in this case -- the LIKE operator or a simple equality test should work fine:

SELECT surname FROM tblname WHERE LOWER(surname) LIKE '$var_surname';

Or:

SELECT surname FROM tblname WHERE LOWER(surname) = '$var_surname';

And please sanitize $var_surname before using it in your query -- use mysql_real_escape_string or Prepared Statements!

edited Feb 23, 2011 at 15:35

answered Feb 23, 2011 at 15:29

Donut

113k20 gold badges136 silver badges147 bronze badges

3 Comments

Frank Nwoko Over a year ago

Tried LIKE and = Not validating.Remember I am working with strings.

Donut Over a year ago

@Frank How does $var_surname get created?

Justin Morgan Over a year ago

@Frank: LIKE and = should both work on MySQL string values. Have you tried validating SELECT surname FROM tblname WHERE LOWER(surname) = 'test'; on your database server? The validation failure probably isn't coming from where you think it is.

Collectives™ on Stack Overflow

Mysql REGEX Matching query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related