Monday, June 13, 2011

How to Disable/Enable Triggers on Demand


One of the sorely wanted features missing in MySQL is the ability to disable/enable triggers on the fly. By comparison, disabling/enabling Foreign Key constraints can be simply done by setting a server system variable:


Now as of version 5.6, there is no built-in server system variable TRIGGER_CHECKS in MySQL. A simple workaround is to instead use a user-defined session variable. The setting for trigger checks stored into the session variable allows the setting to be seen by all statements, including all stored procedures and functions, as long as the user is connected, which for this workaround is in effect similar to using a server system variable.

Besides a session variable that switches the checks for all triggers, to make the control more flexible, we can also set a session variable for each trigger type. There is a total of 6 trigger types, a combination of [BEFORE|AFTER] with [INSERT|UPDATE|DELETE]. The total to consider would be 7 session variables:

#Affects all triggers 
#FALSE value overrides trigger type settings

#Trigger type settings

For the disable/enable logic, the only value for the session variables that matters is the FALSE value. Anything that isn't FALSE or 0 is considered TRUE. Even NULL values are to be considered TRUE. It makes sense that the default value for trigger checks should be TRUE, and when the user disconnects, the session variable is reset back to NULL which re-enables trigger checking.

Implementing the ability to disable/enable on existing triggers could be hard if there are several hundred tables that need to be altered and with each table having their own unique triggers. This could especially be painful if there is no easy way to automate the updating of those existing triggers.

Triggers are special stored procedures and to implement the trigger disable/enable functionality, a simple logic needs to be inserted at the BEGIN statement. Here's an example of an existing BEFORE-INSERT TRIGGER type that has been patched so that it can be disabled/enabled:

DROP TRIGGER IF EXISTS `yearCheck_beforeInsert` $$
CREATE DEFINER=`root`@`localhost` TRIGGER `yearCheck_beforeInsert`

#Patch starts here
thisTrigger: BEGIN
    AND (USER() = 'root@localhost')
    LEAVE thisTrigger;

#Original trigger body starts here
END $$

Here's the rundown on the workaround code patch:
  • The BEGIN statement needs to have a label in order to LEAVE the TRIGGER if the trigger checking is SET to FALSE. This facilitates the patching of the functionality into existing TRIGGERs without needing to wrap the original body of the TRIGGER inside the IF-THEN construct.
  • The appropriate session variable trigger type needs to be correctly chosen, in order for the specific control variable to be able to turn on/off all the triggers intended for it. In this example, it is correctly coded as  @TRIGGER_BEFORE_INSERT_CHECKS.
  • We wouldn't want just any user to be able to disable TRIGGERs. Unfortunately, GRANT PRIVILEGES can't cover this case. Either we would need to hardcode the specific USER/s that can disable triggers into the TRIGGER procedure in order to have security or allow only USERs that have the SUPER privilege since disabling triggers is an administrative operation (see this comment for the condition change).

Triggers can CALL stored procedures and its possible that the trigger logic be moved into an external SP. However, this is not recommended since it would not be possible to use the special trigger-specific variables OLD.<columnName> and NEW.<columnName> in the SP.

When the database administrator logs into the MySQL server under the user 'root@localhost', they can run DML statements without invoking any trigger by setting the @TRIGGER_CHECKS session variable to FALSE. Once done, setting it back to TRUE re-enables triggers and they can continue with other tasks that require triggers to fire. They can simply disconnect immediately without setting the check back to TRUE since in their next session the triggers are enabled by default.

Another workaround on a missing MySQL functionality, support for dynamic cursors, can be found here.

Sunday, June 12, 2011

Truly Random and Complex Password Generator - Part 2 of 2


In the first part of this entry, it was shown how its possible that a password from a normal user would significantly be weaker than that from a complex and randomly generated one.

Note: in the strictest sense, there is no such thing as an uncrackable password. Passwords can be uncrackable only in theory, i.e. the mathematical probability of a password being guessed correctly-- no matter how infinitesimally small the odds are, the possibility of a right guess is always present. Also, passwords are uncrackable only technically-- given enormous resources and time any password can be cracked.

Here is a function for a truly random and complex password generator which is based on the formulas given in the first part of this entry:

DROP FUNCTION IF EXISTS `randomPasswordGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomPasswordGenerator`(
  ) RETURNS varchar(64) CHARSET utf8
  DECLARE charDiceRoll TINYINT(2);
  DECLARE randomChar CHAR(1);
  DECLARE randomPassword CHAR(8) DEFAULT '';
    SET charCount = charCount + 1;
    SET charDiceRoll = 1 + FLOOR(RAND() * 94);
    IF (charDiceRoll <= 32)
      SET randomChar = ELT(charDiceRoll,
      '`', '~', '!', '@', '#', '$', '%', '^',
      '&', '*', '(', ')', '-', '=', '_', '+',
      '[', ']', '{', '}', '\\', '/', '|', '?',
      ';', ':', '\'', '"', ',', '.', '<', '>');
    ELSEIF (charDiceRoll >= 33)
      AND (charDiceRoll <= 68)
      SET charDiceRoll = charDiceRoll - 33;
      SET randomChar = CONV(
        10, 36);
      SET charDiceRoll = charDiceRoll - 59;
      SET randomChar = LOWER(
          10, 36)
    END IF;
    SET randomPassword = CONCAT(randomPassword, randomChar);
  UNTIL (charCount = 8)
  RETURN randomPassword;
END $$

This function will return an 8-character password string. Each character has an equal chance of 1/94 to be generated. Given a short period of time and a normal amount of resources, this qualifies as a theoretical technically uncrackable password. It can be modified to return a longer password length or even a random length, say between 8-12 characters long. A separate user defined function, randomRangePicker(), can be used, if refactoring is desired.

The output can be checked with a simple SELECT statement:

SELECT randomPasswordGenerator();

See the first part of this entry or a similar random string/name generator.

Wednesday, June 8, 2011

Truly Random and Complex Password Generator - Part 1 of 2


Skip to the 2nd part for the code snippet.

Its an important matter of security to enforce complex passwords that have a sufficient length. From personal experience, if you ask a normal user to create their own passwords, their passwords will be based on a character set consisting of 36 case-insensitive alphanumeric characters: a-z, 0-9 instead of the full 94 character set typable on all keyboard layouts. Also, most normal users would use dictionary based passwords with a predictable pattern: dictionary words at the beginning and numbers at the end.

Relying solely on the client-side or front-end to enforce the creation of passwords of at least 8 characters long and the use of special characters will not be practical in preventing the use of dictionary words as well as the usage of a certain pattern. Whatever the mechanism is on the client-side, the backend MySQL database should complement it.

Assigning complex passwords to users will, in effect, increase the number of characters from 36 to 94. By making the password randomly generated, the predictability of dictionary words and pattern matching is removed. The number of possible passwords is substantially increased. For an 8-character password string, under a reasonable time limitation, say 6 hours, and using a single modern computer, this results to a theoretical technically uncrackable password:

SELECT FORMAT(POW(32, 8), 0); 
  -- Results to 1,099,511,627,776 possible combinations. Note that the number of possible combinations is greatly reduced when the user limits the password to use dictionary words and pattern matching. This results to a crackable password in a short period of time.  

 SELECT FORMAT(POW(94, 8), 0); 
  -- Results to 6,095,689,385,410,816 possible combinations. By being randomly generated, the number of combinations is not reduced as explained above. This results to a theoretical technically uncrackable password given a short period of time.  

A password generator, to be truly random, should satisfy the following:
  • The character set for the generator should include all the typable characters on any keyboard layout: 

    a-z, A-Z, 0-9,
    and ` ~ ! @ # $ % ^ & * ( ) - = _ + [ ] { } \ / | ? ; : ' " , . < >

    This results to 26 + 26 + 10 + 32 = 94 characters.
  • Each of the allowed characters should all have an equal chance of being generated.

For practical purposes, we'll take aside arguments on password complexity versus password length, and we'll assume an 8-character password string. To generate any of the 62 alphanumeric characters, we'll use a base 36 statement as the formula:

            RAND() * 36),
      10, 36);

Using a base 36 statement gives us the most compact alphanumeric numeral system. The case sensitivity will be based on odds from a random number range in order to include the LOWER case of the alphabet.

The special characters can be generated by using the ELT function as the basis for the formula like:

      '`', '~', '!', '@', '#', '$', '%', '^',
      '&', '*', '(', ')', '-', '=', '_', '+',
      '[', ']', '{', '}', '\\', '/', '|', '?',
      ';', ':', '\'', '"', ',', '.', '<', '>');

In the continuation of this entry is an example of a true random and complex password generator function.

Sunday, June 5, 2011

How to compare the record differences of two similar tables - Part 2 of 2


The rationale behind comparing tables versus using a CHECKSUM TABLE statement can be found in the first part of this entry.

Comparing the record differences of two similar tables can be useful when transferring records from an old database to a new one or when comparing backup tables against the original tables. Depending on specific requirements, it may be necessary to validate that the transfer was successful or to see which specific data in the records of the original and in-use tables have been updated, inserted, or deleted when compared to the backup. The query in the stored procedure below will show the differences caused by updates to current records as well as the record differences caused when new records are inserted or old ones are deleted in one table but not in the other:

CREATE DEFINER=`root`@`localhost` PROCEDURE `compareTables`(
  dbTableNameA VARCHAR(133),
  dbTableNameB VARCHAR(133))
  DECLARE dbTableA, dbTableB VARCHAR(129);
  DECLARE dbA, tableA, dbB, tableB VARCHAR(64);
  DECLARE columnList TEXT;
  SET dbTableA = REPLACE(dbTableNameA, '`', '');
  SET dbTableB = REPLACE(dbTableNameB, '`', '');
  SET dbA = LEFT(dbTableA, LOCATE('.', dbTableA) - 1);
  IF (dbA = '')
    SET dbA = DATABASE();
  SET tableA = RIGHT(dbTableA, LENGTH(dbTableA) - LOCATE('.', dbTableA));
  SET dbB = LEFT(dbTableB, LOCATE('.', dbTableB) - 1);
  IF (dbB = '')
    SET dbB = DATABASE();
  SET tableB = RIGHT(dbTableB, LENGTH(dbTableB) - LOCATE('.', dbTableB));
  SET columnList = getColumnList(dbA, tableA);
  SET @compareTablesStmt = CONCAT(
  'SELECT MIN(`compareTables`) AS \'Tables Compared\', ',
     columnList, '
  FROM (
    SELECT \'', dbTableNameA, '\' AS `compareTables`, ',
     columnList, '
    FROM `', dbA, '`.`', tableA, '`
    SELECT \'', dbTableNameB, '\' AS `compareTables`, ',
     columnList, '
    FROM `', dbB, '`.`', tableB, '`)
  AS `compareTables`
  GROUP BY ', columnList, '
  HAVING COUNT(*) = 1'
  PREPARE compareTablesStmt FROM @compareTablesStmt;
  EXECUTE compareTablesStmt;
  DEALLOCATE PREPARE compareTablesStmt;
END $$

The stored procedure can be called by passing the two tables to be compared as parameters and can be in the forms: `db`.`table`, db.table, `table`, or table. Passing the names wrapped in backticks and specifying from which database the table is from is optional. If no database is specified, the SP will use the current one. The SP is dependent on a user defined function to get the column names dynamically. The getColumnList() function can be found here.

For TEMPORARY TABLES, you will have to manually input the columns of the tables compared. This cannot be done dynamically purely inside MySQL since as stated in a previous blog entry: "TEMPORARY TABLEs are invisible from the `information_schema` database (as of MySQL version 5.6)".

Here is a sample call:

 CALL compareTables(
 '`origDB`.`origTable`', 'backupTable'

And a sample result set:

Tables ComparedcustomerNumbercontactLastNamecontactFirstName

The first two rows compares the records with the Primary Key or ID = 103 and shows that the data under `contactLastName` are different between the original and backup tables. The third row shows a record that is present in the original table but not in the backup table.

See part 1 of this entry for the rationale behind the SELECT statement using a UNION ALL clause used by the stored procedure.

Thursday, June 2, 2011

How to compare the record differences of two similar tables - Part 1 of 2


Click here to skip to the code snippet.

The CHECKSUM TABLE result sets of two similar tables only indicate if there are differences between the two tables. It does not tell you what exactly the differences are between the two. Does `tableA` have an updated value for one of its records that `tableB` does not have? Or does `tableA` have an extra row that `tableB` does not have? Depending on specific business requirements, the CHECKSUM TABLE statement may be sufficient. If you need to determine what the actual differences are, there is a way to go about doing this automatically and dynamically by creating a stored procedure.

This SELECT statement that uses a UNION ALL clause provides the same functionality in simply determining if there are differences between two tables:

    SELECT *
    FROM (
      SELECT *
      FROM `tableA`
      SELECT *
      FROM `tableB`)
    AS `compareTables`
    GROUP BY `column1`, `column2`, `columnN`
    HAVING COUNT(*) = 1),

This statement provides an automated check in the sense that you won't have to manually compare the CHECKSUMs of `tableA` and `tableB`. Also, unlike using a CHECKSUM TABLE statement, a SELECT statement allows the result to be stored INTO a variable. If the statement returns TRUE then there is a difference found between the two tables, if not it will return FALSE. The GROUP BY clause will need a comma-separated list of all the columns of the tables since MySQL will not permit an asterisk (*) wildcard in this part of the statement.

To fully automate the above formula, it can be converted into a function and the writing of the column names for the GROUP BY clause can be retrieved using the getColumnList() user defined function. Then re-write the SELECT statement as a dynamic PREPARE-EXECUTE construct. The two tables `tableA` and `tableB` can be the parameters to be passed and the TRUE/FALSE values to be the RETURN.

To determine what the actual differences are, a more complex SELECT statement is needed. Based on the first iteration, we can derive from it and make it more useful:

SELECT MIN(`compareTables`) AS 'Tables Compared',
  `column1`, `column2`, `columnN`
  SELECT '`tableA`' AS `compareTables`,
    `column1`, `column2`, `columnN`
  FROM `tableA`
  SELECT '`tableB`' AS `compareTables`,
    `column1`, `column2`, `columnN`
  FROM `tableB`)
AS `compareTables`
GROUP BY `column1`, `column2`, `columnN`

A sample result set will look like this:

Tables ComparedcustomerNumbercontactLastNamecontactFirstName

The first two rows compared `tableA` and `tableB`. Note that the difference between the two tables was found in the values for the `contactLastName` column. The last row shows `tableA` with no matching comparison with `tableB`. This means that the record in `tableA` does not exist in `tableB`.

In the continuation of this entry, a stored procedure that automates the writing of the SELECT statement is given. The names of the tables to be compared and their column names can be dynamically generated so that the SELECT statement won't have to be re-written for each different case that a comparison of two similar tables is made.