MySQL/MariaDB identifiers are names for databases, tables, columns, etc. They can be quoted with `backticks` (AKA backquotes), and in that case they can contain characters which are normally illegal for identifiers (even spaces or the backtick itself), or they can be reserved words. Both quoting and not quoting cause some problems.
If you don’t quote names you will need to avoid illegal chars and reserved words – which is a good practice, anyway. But when you upgrade MariaDB, the new version could add some reserved words.
If you quote names, you should be sure to do it everywhere. It is a good practice, but if a developer doesn’t use backticks (or forgets to do it), he may see strange errors.
If you use keywords as identifiers and don’t quote them, you will probably receive an error 1064 (
mysql> SELECT * FROM insert; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert' at line 1
The bad identifier is usually the first word in the reported string (see example above), but can also be the last word before the beginning of the reported code part:
mysql> SELECT high_priority FROM t; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'FROM t' at line 1
In theory, a query involving unquoted identifiers which are reserved words, could even do something different from what you think and report no errors.
Another caveat: even when you quote all identifiers, avoid using the same name for a column and for a local variable in a Stored Routine. While MariaDB understands clauses like:
WHERE `id` = `id`, this can cause a lot of confusion.
Here are 2 Stored Routines that can probably be useful. For both the routines, I’ll include a Base Test for STK/Unit. The Stored Routines will go into a DB called
lib, so the BTs will go into a Test Case called
The first Function quotes an identifier for inclusion in a SQL string (which can be
EXECUTEd). It’s similar to the
QUOTE() built-in function, that works for strings.
CREATE FUNCTION `lib`.`quote_name`(`id` TEXT) RETURNS TEXT DETERMINISTIC NO SQL LANGUAGE SQL COMMENT 'Return a quoted identifier (if NULL, id is empty)' BEGIN IF `id` IS NULL THEN RETURN '``'; ELSE RETURN CONCAT('`', REPLACE(`id`, '`', '``'), '`'); END IF; END; CREATE PROCEDURE `test_lib`.`test_quote_name`() LANGUAGE SQL COMMENT 'Test quote_name()' BEGIN CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`('x'), '`x`', 'Incorrect quoting'); CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`('x`y'), '`x``y`', 'Incorrect escape'); CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`(''), '``', 'Empty name expected'); CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`(NULL), '``', 'For NULL value, empty name should be returned'); END;
If you need to verify a single name on the fly, the easiest way is probably:
SELECT 0 AS [name];
The following Procedure, however, is meant to be used inside Stored Programs. It can be used to know if a string is a valid name. It executes the above query (as a Prepared Statement, because it must be composed dynamically) and checks wether an error is generated.
CREATE PROCEDURE `lib`.`is_valid_name`(IN `id` TEXT, OUT `ret` BOOL) NO SQL LANGUAGE SQL COMMENT 'set `ret` to TRUE if id is valid name, else FALSE' BEGIN -- error in query DECLARE EXIT HANDLER FOR 1064 BEGIN SET `ret` = FALSE; END; SET @sql_query = CONCAT('DO (SELECT 0 AS ', `id`, ');'); PREPARE stmt FROM @sql_query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET `ret` = TRUE; END; CREATE PROCEDURE `test_lib`.`test_is_valid_name`() LANGUAGE SQL COMMENT 'Test quote_name()' BEGIN CALL `lib`.`is_valid_name`('valid_name', @r); CALL `stk_unit`.`assert_true`(@r, 'Specified name is valid'); CALL`lib`.`is_valid_name`('SELECT', @r); CALL `stk_unit`.`assert_false`(@r, 'SELECT is a keyword'); CALL `lib`.`is_valid_name`('a a', @r); CALL `stk_unit`.`assert_false`(@r, 'Valid names cant contain spaces'); CALL `lib`.`is_valid_name`('', @r); CALL `stk_unit`.`assert_false`(@r, 'Empty name is not valid'); END;