I often find that when I get access to a DB I dont know how to control it, I always end up typing in the wrong commands or having to quickly google what i want to do. Thus, I thought it would make sense to write up a quick cheat sheet, especially when sometimes with SQLi you need to be as careful with your commands as possible.
This write up will be in the prespective of a dirtect connection to the DB.
To login from a linux terminal (use -h only if connecting to a remote box)
1 | #mysql -h 10.0.0.2 -u root -p |
View Databases and Table info/data
To create a new db on the server
1 | mysql> create database [db name]; |
Show databases
1 | mysql> show databases; |
To delete a db on the server
1 | mysql> drop [db name]; |
Connect to a database
1 | mysql> use [db name]; |
List the tables in a db
1 | mysql> show tables; |
To delete a table
1 | mysql> drop table [table name]; |
Show info about table field formats
1 | mysql> describe [table name]; |
Show columns and column info
1 | mysql> show colums from [table name]; |
Output all data from the table
1 | mysql> SELECT * FROM [table name]; |
Show only rows with the value “searchstring”
1 | mysql> SELECT * FROM [table name] WHERE [field name] = "searchstring"; |
Show all rows that contain both username “admin” and dayslocked ‘0’;
1 | mysql> SELECT * FROM [table name] WHERE username = "admin" AND dayslocked = '0'; |
Show all rows that contain both username like “admin” and dayslocked ‘0’;
1 | mysql> SELECT * FROM [table name] WHERE username = "admin%" AND dayslocked = '0'; |
Show all rows that contain both username not “admin” and locked ‘1’
1 | mysql> SELECT * FROM [table name] WHERE username != "admin" AND locked = '1'; |
Show only rows with the value “searchstring” but only showing records 1-10
1 | mysql> SELECT * FROM [table name] WHERE [field name] = "searchstring" limit 1,10; |
Count number of rows
1 | mysql> SELECT COUNT(*) FROM [table name]; |
Count number of columns
1 | mysql> SELECT SUM(*) FROM [table name]; |
Modify data in tables
Create a table
1 | mysql> create table [table name] (personid int(50) not null auto_increment primary key,fname varchar(35),mname varchar(50),lname varchar(50) default 'blank'); |
New row in a table
1 | mysql> INSERT INTO db (Host,Db,username,locked,dayslocked) VALUES ('%','databasename','pentest','0','0'); |
Modify data in a table
1 | mysql> UPDATE [table name] SET locked = '0' where [field name] = 'pentest'; |
Delete a row from a table
1 | mysql> DELETE from [table name] where [field name] = 'searchstring'; |
Delete a column from a table
1 | mysql> alter table [table name] drop column [column name]; |
Change column name
1 | mysql> alter table [table name] change [oldcolumnname] [newcolumnname] varchar (25); |
Make a column bigger
1 | mysql> alter table [table name] modify [columnname] VARCHAR(30); |
Make a unique column so you get no duplicates
1 | mysql> alter table [table name] add unique ([columnname]); |
backup and Restoring data
Load CSV into a table
1 | mysql> LOAD DATA INFILE '/root/backup.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3); |
Dump all databases and data into a backup sql file (contains sql commands to recreate all dbs)
1 | #mysqldump -u root -pmysecret --opt > /root/backup.sql |
Dump a single database for backup
1 | #mysqldump -u root -pmysecret --databases [db name] > /root/dbname_backup.sql |
Dump a single table for backup
1 | #mysqldump -c -u root -pmysecret [db name] [table name] > /root/dbnamee.tablename.sql |
Restore from the backup
1 | #mysql -u root -pmysecret [db name] < /root/dbname_backup.sql |
User info
Create a new user (switch to mysql db, make user, then giv privs)
1 2 3 | mysql> use mysql; mysql> INSERT INTO user (Host,User,Password) VALUES ('%','pentest',PASSWORD('mysecret')); mysql> flush privileges; |
Change user password, both from linux terminal and mysql prompt
1 2 | #mysqladmin -u pentest -h 10.0.0.2 -p password 'mysecret2' mysql> SET PASSWORD FOR 'pentest'@'10.0.0.2' = PASSWORD('mysecret2'); |
Create a password for user if there is not one currently set (warning as password will be stored in bash history!)
1 | #mysqladmin -u root password mysecret |
Update a password
1 | #mysqladmin -u root -p oldsecret newsecret |
Allow new user to connect to db with privs for a table(do this as root user)
1 2 3 4 | mysql> use mysql; mysql> grant usage on *.* to pentest@localhost identified by 'mysecret'; mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','db name','pentest','Y','Y','Y','Y','Y','N'); mysql> flush privileges |
Or just grant the user access to everything
1 2 | mysql> grant all privileges on databasename.* to pentest@localhost; mysql> flush privileges; |
To update data alreasy in a table
1 | mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'pentest'; |
Thanks to pantz.org for the pointers.
Leave a Reply
You must be logged in to post a comment.