A new beginning with phpMyAdmin

After successfully completing my Google Summer of Code 2014 (GSoC) project, today, I officially became committee member of the phpMyAdmin project unanimously. Thanks to all team members for invitation. I must say that GSoC’14 was a great experience. I learned a lot in those 12 weeks. Special thanks to my mentor Isaac Bennetch for the guidance throughout the project. I would also like to thank other team members & developers: Marc Delisle (lem9), Michal Čihař (nijel), Hugues Peccatte (Tithugues), Madhura Jayaratne (madhuracj), Atul Pratap Singh (zixtor) for the support.

GSoC 2014 Week #12: Improved notification when attempting to insert invalid data

Week #12 Task:  Improved notification when attempting to insert invalid data
Scheduled Deadline: August 04, 2014
Completed On: August 04, 2014

How it was done: In this week, I improved the validation of various fields:

  • Integer type column fields: Considered range of all integer types (TINYINT, INT, BIGINT, etc.) including SIGNED or UNSIGNED attribute by adding data-min & data-max attributes to the field which specifies the minimum and maximum value of the field respectively which is checked when field value is changed using JavaScript. But as JavaScript supports upto 53 bit numbers (after that approximation comes into play) which was not enough for MySQL point of view which has support for 64 bit Integer also. We needed precision upto 64 bits so the best option was to compare numbers as strings and not as number actually. So, I created a big_ints.js file which is BigInts library of phpMyAdmin. Currently, it has BigInts.compare and BigInts.lpad function only as those were required for this task. The BigInts.compare function compares the two number in string of any size (practically limited to the maximum size of string in JS) which also supports negative numbers as well as numbers with leading zeros for e.g. 0001 when compared with 1 will be considered as equal.
  • AES_ENCRYPT, AES_DECRYPT validation: The output of AES_ENCRYPT and AES_DECRYPT is in binary, so applying these functions to varchar or char types doesn’t makes sense. So, whenever AES_ENCRYPT or AES_DECRYPT is chosen in function column then it checks whether the column is of BINARY or BLOB types. Otherwise it will turn the function column field red denoting that this function is not applicable to current field.
  • Character columns length validation: Earlier HTML maxlength property was used to allow user only to enter that many characters into the field but from the discussion on mailing lists it seemed that it was better to allow user to continue even if we feel that it is not correct and let MySQL truncate the data accordingly. So, I changed maxlength to data-maxlength and used it to check whether length has exceeded the maximum length supported by column and indicate it by turning the field red. I also tested this with multibyte character sets in which one character may take more than one byte for storage and it was found that JS length property handles it efficiently and we can rely on it.
  • MD5 validation: The output of MD5() is a string of 32 hexadecimal digits irrespective of data length. So, using this function on varchar(5) like columns where column size is 5 (less than 32) will cause the result to be truncated and to indicate that we turn the function column field red.

Code: https://github.com/phpmyadmin/phpmyadmin/commit/11ae13f1772ad74aca63e069ca629089534ad58e
Key accomplishments last week: Improved notification when attempting to insert invalid data.
Key tasks that stalled: None.