Doing a pattern match MySQL-side before data is inserted into a table?

Question

I'm new to MySQL, and am wondering: Is it possible to make a table check attempted inserts against some sort of pattern match, and reject any inserts that fail to match the pattern, or must this checking all be done PHP / what-ever-server-side-language side?

I'm thinking specifically about confining an email column in a user table to only be able to contain email addresses using some sort of regex-like pattern matching.

http://stackoverflow.com/questions/16005283/is-it-possible-to-enforce-data-checking-in-mysql-using-regular-expression — bobince, Jun 10 '13 at 21:07
Thanks for the link, @bobince! /goes off to research triggers — Nathan Arthur, Jun 10 '13 at 21:15

score 1 · Accepted Answer · answered Jun 10 '13 at 23:40

1

Because you can do something with a DBMS stored procedure doesn't make it a good idea. I strongly suggest you do this kind of validation in PHP rather than in the DBMS.

PHP's install kit contains a validator for email. See here: http://php.net/manual/en/filter.filters.validate.php

If you do this in your DBMS you have to reinvent the flat tire, er, wheel.

answered Jun 10 '13 at 23:40

O. Jones

103,626
17
118
172

Ok, that makes sense. And thank you for the link to the filter list! Very helpful. – Nathan Arthur Jun 11 '13 at 13:45

score 1 · Answer 2 · edited May 23 '17 at 12:05

Although an answer was already accepted, I believe there are use cases for doing validation on the db side.

It's an extra layer of security. While there is such a thing as overkill I think the case could be made that no matter how well you validate on the application side and use parameter binding with PDO there is always a possibility someone figures out a way to get around it.
If the same data might be used by more than one application, it enforces rules in case a developer for a different app fails to validate correctly.

Here is a example of validation using triggers in mysql. http://cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql/

And here you can see how you might replace the code in those IF statements with regex. regular expressions inside a SQL IF statement

I haven't tried it so don't know if it works but there is a link to the mysql docs on regex. http://dev.mysql.com/doc/refman/5.1/en/regexp.html

And of course you should always validate on the application side too.

Insightful answer, Isimmons. Thank you. +1 – Nathan Arthur Oct 18 '13 at 14:31 — Nathan Arthur, Oct 18 '13 at 14:31

Doing a pattern match MySQL-side before data is inserted into a table?

2 Answers2