avoding key-words while create the table using mysql-proxy

using proxy we can also avoid using reserver work in create table .

Step-1 create the table "mysql.reserved_words" and store all the reserver words.

mysql> use mysql
drop table if exists mysql.reserved_words;

CREATE TABLE mysql.reserved_words (keyword varchar(255) NOT NULL DEFAULT '',   PRIMARY KEY (keyword)) ENGINE=MyISAM ;
INSERT INTO mysql.reserved_words VALUES ('ACCESSIBLE'),('ADD'),('ALL'),('ALTER'),('ANALYZE'),('AND'),('AS'),('ASC'),('ASENSITIVE'),('BEFORE'),('BETWEEN'),('BIGINT'),('BINARY'),('BLOB'),('BOTH'),('BY'),('CALL'),('CASCADE'),('CASE'),('CHANGE'),('CHAR'),('CHARACTER'),('CHECK'),('COLLATE'),('COLUMN'),('CONDITION'),('CONSTRAINT'),('CONTINUE'),('CONVERT'),('CREATE'),('CROSS'),('CURRENT_DATE'),('CURRENT_TIME'),('CURRENT_TIMESTAMP'),('CURRENT_USER'),('CURSOR'),('DATABASE'),('DATABASES'),('DAY_HOUR'),('DAY_MICROSECOND'),('DAY_MINUTE'),('DAY_SECOND'),('DEC'),('DECIMAL'),('DECLARE'),('DEFAULT'),('DELAYED'),('DELETE'),('DESC'),('DESCRIBE'),('DETERMINISTIC'),('DISTINCT'),('DISTINCTROW'),('DIV'),('DOUBLE'),('DROP'),('DUAL'),('EACH'),('ELSE'),('ELSEIF'),('ENCLOSED'),('ESCAPED'),('EXISTS'),('EXIT'),('EXPLAIN'),('FALSE'),('FETCH'),('FLOAT'),('FLOAT4'),('FLOAT8'),('FOR'),('FORCE'),('FOREIGN'),('FROM'),('FULLTEXT'),('GRANT'),('GROUP'),('HAVING'),('HIGH_PRIORITY'),('HOUR_MICROSECOND'),('HOUR_MINUTE'),('HOUR_SECOND'),('IF'),('IGNORE'),('IN'),('INDEX'),('INFILE'),('INNER'),('INOUT'),('INSENSITIVE'),('INSERT'),('INT'),('INT1'),('INT2'),('INT3'),('INT4'),('INT8'),('INTEGER'),('INTERVAL'),('INTO'),('IS'),('ITERATE'),('JOIN'),('KEY'),('KEYS'),('KILL'),('LEADING'),('LEAVE'),('LEFT'),('LIKE'),('LIMIT'),('LINEAR'),('LINES'),('LOAD'),('LOCALTIME'),('LOCALTIMESTAMP'),('LOCK'),('LONG'),('LONGBLOB'),('LONGTEXT'),('LOOP'),('LOW_PRIORITY'),('MASTER_SSL_VERIFY_SERVER_CERT'),('MATCH'),('MEDIUMBLOB'),('MEDIUMINT'),('MEDIUMTEXT'),('MIDDLEINT'),('MINUTE_MICROSECOND'),('MINUTE_SECOND'),('MOD'),('MODIFIES'),('NATURAL'),('NOT'),('NO_WRITE_TO_BINLOG'),('NULL'),('NUMERIC'),('ON'),('OPTIMIZE'),('OPTION'),('OPTIONALLY'),('OR'),('ORDER'),('OUT'),('OUTER'),('OUTFILE'),('PRECISION'),('PRIMARY'),('PROCEDURE'),('PURGE'),('RANGE'),('READ'),('READS'),('READ_WRITE'),('REAL'),('REFERENCES'),('REGEXP'),('RELEASE'),('RENAME'),('REPEAT'),('REPLACE'),('REQUIRE'),('RESTRICT'),('RETURN'),('REVOKE'),('RIGHT'),('RLIKE'),('SCHEMA'),('SCHEMAS'),('SECOND_MICROSECOND'),('SELECT'),('SENSITIVE'),('SEPARATOR'),('SET'),('SHOW'),('SMALLINT'),('SPATIAL'),('SPECIFIC'),('SQL'),('SQLEXCEPTION'),('SQLSTATE'),('SQLWARNING'),('SQL_BIG_RESULT'),('SQL_CALC_FOUND_ROWS'),('SQL_SMALL_RESULT'),('SSL'),('STARTING'),('STRAIGHT_JOIN'),('TABLE'),('TERMINATED'),('THEN'),('TINYBLOB'),('TINYINT'),('TINYTEXT'),('TO'),('TRAILING'),('TRIGGER'),('TRUE'),('UNDO'),('UNION'),('UNIQUE'),('UNLOCK'),('UNSIGNED'),('UPDATE'),('USAGE'),('USE'),('USING'),('UTC_DATE'),('UTC_TIME'),('UTC_TIMESTAMP'),('VALUES'),('VARBINARY'),('VARCHAR'),('VARCHARACTER'),('VARYING'),('WHEN'),('WHERE'),('WHILE'),('WITH'),('WRITE'),('XOR'),('YEAR_MONTH'),('ZEROFILL'), ('ACTION'), ('BIT'), ('DATE'), ('ENUM'), ('NO'), ('TEXT'), ('TIME'), ('TIMESTAMP'), ('READ_ONLY');

step 2: create the procedure for checking the reserve word.

DELIMITER ;
DROP PROCEDURE  if exists security_create_table;
DELIMITER //
CREATE PROCEDURE security_create_table(in_name varchar(255))
BEGIN
DECLARE l_cnt INT default 0;
    SELECT count(*) into l_cnt from reserved_words where keyword = in_name;
    IF l_cnt <> 0 THEN
        set @psql= CONCAT('forbidden key word in a create table : ', in_name);
        PREPARE stmt FROM @psql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END
//
DELIMITER ;

Then the lua script are as follows:

--If you want to block certain words as table names then you will have to modify the script.
local tokenizer = require("proxy.tokenizer")
function read_query(packet)
        if packet:byte() == proxy.COM_QUERY then
                local tokens = tokenizer.tokenize(packet:sub(2))
                -- just for debug
                for i = 1, #tokens do
                        -- print the token and what we know about it
                        local token = tokens[i]
                        local txt = token["text"]
                        if token["token_name"] == 'TK_STRING' then
                                txt = string.format("%q", txt)
                        end
                        -- print(i .. ": " .. " { " .. token["token_name"] .. ", " .. token["text"] .. " }" )
                        print(i .. ": " .. " { " .. token["token_name"] .. ", " .. txt .. " }" )
--code added
-- grab the table name from create table statement
    if i > 2 and tokens[i-1]["token_name"]=='TK_SQL_TABLE' and tokens[i-2]["token_name"]=='TK_SQL_CREATE' then
    if token["token_name"]=='TK_STRING' or token["token_name"]=='TK_LITERAL' then
   
-- check for single word
      if txt == 'india' then
      print ("india detected")
      proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "you should not use reserved words ".. "[" .. txt .. "]" ..  " in create table" )
      return proxy.PROXY_SEND_QUERY
      end
--[[
-- table name checked against reserved_words list from mysql DB
    print ("checking")
    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "call mysql.security_create_table('"..txt.."');")
    return proxy.PROXY_SEND_QUERY
--]]
     end
     end
--addition complete
        end
                print("normalized query: " .. tokenizer.normalize(tokens))
        print("")
        end
end

step4: modify the mysql-proxy,cnf file which is as follows:
Note: give the correct path of the file

proxy-lua-script = /home/kiran.chinta/mysql-proxy-0.8.1-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/reserveword1.lua

step:5 restart mysql proxy

sh /home/kiran.chinta/mysql-proxy-0.8.1-linux-glibc2.3-x86-64bit/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

#try to connect to mysql on port 4040
mysql> create table india (eno int );
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 'you should not use reserved words [india] in create table' at line 1

if you want to add more reserver word keep inserting the values on mysql.reserved_words table.