MySQL tips and tricks

Here are some useful reference for MySQL command line. This file does not contain anything related to server configuration

The official documentation is available at: http://dev.mysql.com/doc/

Basic commands

The basic MySQL commands are:

• SHOW DATABASES to list available databases,

• USE database to tell the MySQL client to use a specific database,

• SHOW TABLES to list tables in the current database,

• DESCRIBE table to get a list of fields of a given table, and

• SELECT * FROM table LIMIT 10 to get 10 rows from the given table.

Once a database is selected, standard SQL queries are possible.

Account management

To create a new MySQL user and give her a special database, which is useful for example when creating a new website, three commands are needed:

CREATE USER 'newsite'@'localhost' IDENTIFIED BY 'a-random-password';
CREATE DATABASE newsite DEFAULT CHARACTER SET 'utf8';
GRANT SELECT, INSERT, DELETE, UPDATE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON newsite.* TO 'newsite'@'localhost';
FLUSH PRIVILEGES;

The following command changes the password associated to the user:

SET PASSWORD FOR 'newsite'@'localhost' = PASSWORD('an other random password!');

To list all users, you need SELECT access to mysql database:

SELECT User, Host FROM mysql.user;

The permissions of a specific user can be seen with SHOW GRANTS. Here is an example:

mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*Hashed password' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                  |
+---------------------------------------------------------------------------------------------------------------+

Shell connection

To connect to a database when being in a shell, use the mysql command:

mysql -H server -u user -p database

This command asks for the password of user and then connects and use the specified database.

To give the password directly on the command line, do not put a space between -p and the password:

mysql -u user "-p$PASSWORD" database

It is also possible to put login information in a hidden file in your home folder, in ~/.my.cnf. Such file looks like this:

[mysql]
user=mysqluser
password=password-for-mysqluser
database=mydb

To dump a database, the command is mysqldump. Here are some examples:

mysqldump --lock-tables database mytable
mysqldump --single-transaction --add-drop-table database_with_innoDB

List of permissions

This table lists the available permissions, used by GRANT:

Data	Structure	Administration
SELECT	CREATE	GRANT
INSERT	ALTER	SUPER
UPDATE	INDEX	PROCESS
DELETE	DROP	RELOAD
FILE	CREATE TEMPORARY TABLES	SHUTDOWN
	SHOW VIEW	SHOW DATABASES
	CREATE ROUTINE	LOCK TABLES
	ALTER ROUTINE	REFERENCES
	EXECUTE	REPLICATION CLIENT
	CREATE VIEW	REPLICATION SLAVE
	EVENT	CREATE USER
	TRIGGER

MySQL query examples

These queries create an empty account table with some fields:

DROP TABLE IF EXISTS `account`;
CREATE TABLE IF NOT EXISTS `account` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `hruid` varchar(255) CHARACTER SET ascii NOT NULL,
  `name` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `admin` tinyint(1) NOT NULL DEFAULT '0',
  `birthday` datetime NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `hruid` (`hruid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When the user is allowed to interact with the filesystem:

-- Read a file from the filesystem
SELECT LOAD_FILE('/etc/hosts');

-- Write a file that does not exist
SELECT 'Hello, world!' INTO OUTFILE '/tmp/my_hello.txt';

SQL injection queries

When a SQL injection vulnerability hits an application and makes it possible to run arbitrary SELECT queries, here are some queries that can be used in order to gather information about the database.

• Which version is the server running?

  SELECT version();
  SELECT @@version;
  

• What are the available tables?

  SELECT CONCAT(IF(TABLE_CATALOG,TABLE_CATALOG,''),':',
                TABLE_SCHEMA,'.',TABLE_NAME)
         FROM information_schema.TABLES;
  

• What are the columns in the tables?

  SELECT CONCAT(IF(TABLE_CATALOG,TABLE_CATALOG,''),':',
                TABLE_SCHEMA,'.',TABLE_NAME,'.',
                COLUMN_NAME,'(',DATA_TYPE,', ',COLUMN_TYPE,')')
         FROM information_schema.COLUMNS;
  

• What are the privileges visible from the current user?

  SELECT CONCAT(GRANTEE,'=',IF(TABLE_CATALOG,TABLE_CATALOG,''),',',
                PRIVILEGE_TYPE,IF(IS_GRANTABLE,'_grantable',''))
         FROM information_schema.USER_PRIVILEGES;
  SELECT CONCAT(GRANTEE,'=',IF(TABLE_CATALOG,TABLE_CATALOG,''),':',
                TABLE_SCHEMA,',',
                PRIVILEGE_TYPE,IF(IS_GRANTABLE,'_grantable',''))
         FROM information_schema.SCHEMA_PRIVILEGES;
  SELECT CONCAT(GRANTEE,'=',IF(TABLE_CATALOG,TABLE_CATALOG,''),':',
                TABLE_SCHEMA,'.',TABLE_NAME,',',
                PRIVILEGE_TYPE,IF(IS_GRANTABLE,'_grantable',''))
         FROM information_schema.TABLE_PRIVILEGES;
  SELECT CONCAT(GRANTEE,'=',IF(TABLE_CATALOG,TABLE_CATALOG,''),':',
                TABLE_SCHEMA,'.',TABLE_NAME,'.',COLUMN_NAME,',',
                PRIVILEGE_TYPE,IF(IS_GRANTABLE,'_grantable',''))
         FROM information_schema.COLUMN_PRIVILEGES;
  

• Who gave the grants and when?

  SELECT CONCAT(User,'@',Host,':',Db,'.',Table_name,':',Grantor,' ',
                Timestamp,' ',Table_priv,' ',Column_priv)
         FROM mysql.tables_priv;
  

• What are the password hashes?

  SELECT CONCAT(User,'@',Host,':',Password,
                ' (grant=',Grant_priv,',super=',Super_priv,')')
         FROM mysql.user;