How to prevent using digits in a VARCHAR column using mysql?

Question

I have a table called user, and it would be queried both by user_id and username. whilst user_id is of type int, username (being of type varchar) should not only contain digits. The program could help checking the input, but I want to double secure it by not allowing purely numbers already on the level of the database.

How to make that happen?

use a integer type for user_id, so you can only store numeric data — Bernd Buffen, May 22 '16 at 09:57
@BerndBuffen My question is I don't want to have numberic data (in varchar) — Weijing Jay Lin, May 22 '16 at 10:33

score 0 · Answer 1 · answered May 22 '16 at 10:18

0

You can use isNaN() function to check the input value of user_name.

Click here for read about isNAN()

answered May 22 '16 at 10:18

Mujahidh

428
4
19

I mean by MySQL system, is there a way the check it? – Weijing Jay Lin May 22 '16 at 10:34
better ensure a validation on client side and send it server side.Its always good practice – Mujahidh May 22 '16 at 10:35
1

Yes, I understand, but I mean I want to make it in database level (after programmatic checking; the client site can't even secure, a entry level hacker could easily modify the script and submit) – Weijing Jay Lin May 22 '16 at 10:38

score 0 · Accepted Answer · edited May 23 '17 at 11:59

You are looking for the CHECK SQL operator. Unfortunately, this one is not supported by any of the MySQL engines.

There is a workaround, though, by making use of SQL triggers. You may find more information at CHECK constraint in MySQL is not working and http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

So, for me (MySQL 5.5+ required), the following snipplet worked:

use test;

CREATE TABLE user (
  `user_id` INT NOT NULL,
  `username` VARCHAR(50) NULL,
  PRIMARY KEY (`id`)
);

delimiter //

create trigger trg_trigger_test_ins before insert on user
for each row
begin
    if new.username REGEXP '^[0-9]*$' then
        signal sqlstate '45000' set message_text = 'Invalid username containing only digits';
    end if;
end
//

delimiter ;

insert into user values(1, '1234');

The last INSERT statement raised an Error Code: 1644. Invalid username containing only digits whilst

insert into `test`.`user` values(1, 'a1234');

was inserted properly.

NB: Some kudos also to Throw an error in a MySQL trigger on the signal idea.

Note that this example only protects against INSERTs. If you want to be safe, you also need to implement a trigger on UPDATE.

How to prevent using digits in a VARCHAR column using mysql?

2 Answers2

Linked