MySQL

MySQL Connector/Python automatically appends a semicolon at the end of your queries.

Operator Precedence - Highest to lowest

1. Parentheses ()

Operations inside parentheses are evaluated first.

1. Unary Operators

+ (positive)

- (negation)

~ (bitwise NOT)

! (logical NOT)

1. Multiplication, Division, Modulus: These operators are evaluated next and have the same precedence:

* (multiplication)

/ (division)

% (modulus)

1. Addition and Subtraction: These operators have the next level of precedence:

+ (addition)

- (subtraction)

1. Comparison Operators: All of these operators have the same precedence:

= (equal to)

!= (not equal to)

> (greater than)

< (less than)

>= (greater than or equal to)

<= (less than or equal to)

LIKE (pattern matching)

1. Logical NOT !

1. Logical AND &&

1. Logical OR ||

General Commands

Connect to the database

mysql -u USER -h HOST -P PORT -p

Print available databases

show databases;

Connect to the database

use databasename;

Tables

Print tables from the database

show tables;

Print info about the table

describe table_name;

Add values to table

INSERT INTO table_name VALUES (value_1,..);

Add values to column in a table

INSERT INTO table_name(column2, ...) VALUES (column2_value, ..);

Update table values

UPDATE table_name SET column1=newvalue1, ... WHERE <condition>;

Columns

Show all columns in a table

select * from table_name;

Show columns from a table

select name,username,password from sd4fg_users;

Delete a table

DROP TABLE tablename;

Add a new column

ALTER TABLE logins ADD newColumn INT;

Rename column

ALTER TABLE logins RENAME COLUMN newColumn TO oldColumn;

Change column datatype

ALTER TABLE logins MODIFY oldColumn DATE;

Delete column

ALTER TABLE logins DROP oldColumn;

Output

Sort By column

SELECT * FROM logins ORDER BY column_1;

Sort by column in descending order

SELECT * FROM logins ORDER BY column_1 DESC;

Sort by column in Ascending order

SELECT * FROM logins ORDER BY column_1 DESC, id ASC;

Sort by two-columns

SELECT * FROM logins LIMIT 2;

Only show first two results starting from index 2

SELECT * FROM logins LIMIT 1, 2;

List results that meet a condition

SELECT * FROM table_name WHERE <condition>;

List results where the name is similar to a given string

SELECT * FROM logins WHERE username LIKE 'admin%';

pymysql

• The pymysql library is a Python client for interacting with MySQL databases.

The following script dynamically executes SQL queries on a target database using credentials extracted from application settings:

#!/usr/bin/env python

import pymysql
import sys
from craft_api import settings

# Test connection to MySQL database
connection = pymysql.connect(
    host=settings.MYSQL_DATABASE_HOST,
    user=settings.MYSQL_DATABASE_USER,
    password=settings.MYSQL_DATABASE_PASSWORD,
    db=settings.MYSQL_DATABASE_DB,
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with connection.cursor() as cursor:
        sql = sys.argv[1]
        cursor.execute(sql)
        result = cursor.fetchall()
        print(result)
finally:
    connection.close()

Use Cases in Exploitation

python myscript.py "SHOW TABLES"

python myscript.py "SELECT * FROM user"

python myscript.py "SHOW GRANTS FOR CURRENT_USER()"

python myscript.py "DESCRIBE user"