How can I check string length before insert in MySQL?

Question

I created a table with the following statements in MySQL.

CREATE TABLE Users (
   UserName VARCHAR(15) NOT NULL PRIMARY KEY,
   Password VARCHAR(15) NOT NULL,
   Active Bool DEFAULT TRUE
)

So I want make a 2 trigger on this table:

Check the UserName and Password to be more than 4 or 8 characters. (Check the minimum length of string in inserting new rows)
Make some regular expression to check the password. For example I want it contains both Upper case and lower case characters and numbers.

Please guide me. Thanks a lot.

As MySQL still doesn't support check constraints you will need a trigger for that. — user330315
– user330315, Commented Feb 21, 2013 at 8:21
Why do you want to devote this task to MySQL? Just do it in your client software. — mvp
– mvp, Commented Feb 21, 2013 at 8:21
@mvp Sometimes the database is used by multiple projects ... not saying this is, but it's an argument :) — Ja͢ck
– Ja͢ck, Commented Feb 21, 2013 at 8:22
You can read up on the correct syntax on how to create triggers in mysql ... what have you tried so far? — Ja͢ck
– Ja͢ck, Commented Feb 21, 2013 at 8:23
@mvp: it's good practise to check these kind of things (also) in the database. For any important database you usually wind up with a lot more than a single application accessing it. — user330315
– user330315, Commented Feb 21, 2013 at 8:23

gbn · Accepted Answer · 2013-02-21 08:24:17Z

2

You can use a stored procedure or trigger to test the data first
MySQL does not support CHECK constraints unlike other RDBMS

The stored procedure approach is more obvious because it tests before insert/update.
Triggers can be quite opaque for code maintenance later

However, I'm more concerned about having plain text passwords in the database. Where is the salt and hash you'd normally use etc?

answered Feb 21, 2013 at 8:24

gbn

434k84 gold badges602 silver badges690 bronze badges

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

1 Answer 1