Notes by mraabo

MySQL

Properties1
tagsdatabases

A dialect of Structure Query Language (SQL) widely used for web development and open-source stacks.

MySQL servers often runs on TCP port 3306.

Authentication

The mysql command-line utility is used to interact to and authenticate a MySQL or MariaDB database with:

mysql -u <user> -h <host url or IP> -P <port> -p [OPTIONS]

where -p prompts securely (not saved in bash history) for the password.

Options

  • --skip-ssl-verify-server-cert: disable SSL Verification

Commands

Once authenticated and a mysql instance is entered it is possible to use the following commands, that must end with a semicolon ;.

Server-level (Database management)

  • CREATE DATABASE <db name>: create a database identified by <db name>.
  • SHOW DATABASES: display the list of available databases.
  • DROP DATABASE <db name>: removes <db name> from the server.
  • USE <db name>: sets the default database context for subsequent commands.

Schema-level (Table management)

Within a database (either selected with USE or prefixed to tablename) it is possible to use the following commands:

  • SHOW TABLES: display list of tables in database.
  • DESCRIBE <table name>: display the structure of <table name> with its attributes and their types.
  • DROP TABLE <table name>: remove a table.

Case sensitivity for table names depends on the host operating system:

  • sensitive on Linux
  • insensitive on Windows).

Create table

Furthermore it is possible to create a table with:

CREATE TABLE <table name> (
     <attribute1> <type> <property>,
     <attribute2> <type> <property>,
     ...
     );

while not necessary it is recommended to use a Primary Key to uniquely identify each record, by having one of the lines within CREATE TABLE () with the syntax PRIMARY KEY(<attribute>).

  • Types

    For each attribute <type> can be:

    • INT: integer
    • VARCHAR(<int>):
    • DATETIME:

  • Properties

    For each attribute <property> can be:

    • AUTO_INCREMENT: usable for INT types. Automatically set the attribute for a new record to be the value of that same attribute for the last added record incremented by one. Often used for id Primary Key.
    • NOT NULL: the attribute must have a value and cannot be empty.
    • UNIQE: no records can have the same value of this attribute.
    • DEFAULT <value>: specify that the attribute have the default value <value>.

Alter table

It is also possible to alter a table in several ways with:

ALTER TABLE <table name> <alter command>

where the <alter command> can be:

  • ADD <attribute name> <type>: add a new column with an attribute of a certain type (see types in “create table” section.).
  • RENAME COLUMN <old name> TO <new name>: rename a column
  • MODIFY <attribute> <new type>: change an attributes type.
  • DROP <attribute>: remove attribute.

Data manipulation

To manage records one can use the commands:

Insert

To add a record of values to a table one can use:

INSERT INTO <table name> VALUES (<attr1_value>, <attr2_value>, ...,);

It is possible to skip specifying values of attributes with default values by:

INSERT INTO <table name>(<attr2>, <attr10>, ...) VALUES (<attr2_value>, <attr10_value>, ...);

however, not specifying attributes with the NOT NULL constraint that also lacks a DEFAULT value will result in an error.

Select

The select statement can be used to view the data in a table.

To view the whole table:

SELECT * FROM <table name> [WHERE <condition>] [<clause>];

as * acts as a wildcard.

To view selected columns use:

SELECT attr1, attr2 FROM <table name> [WHERE <condition>] [<clause>];

Where a <clause> can be:

  • ORDER BY <attr1> [ACS | DSC] [, <attr2> [ACS | DSC],...]: order records based on <attr1> is ascending ACS (default) or descending DCS order. With the possibility of ordering on <attr2> for duplicate values of <attr1> and so on.
  • GROUP BY <attribute>: group records based on their value of <attribute>.
  • LIMIT <int>: limit the number of records shown to <int>.

  • Set operators

    Several select statements can be combined with set operators like this:

    <select_statement1> <set_operator> <select_statement2>;

    but only if the query results of the select statements adhere to:

    • having equal number of columns(attributes).
    • having compatible data types in corresponding columns (cannot combine string column in same position as integer column).

    The available set operators are:

    • UNION: combine query results and show unique records (exclude duplicates).
    • UNION ALL: combine query results including all duplicates.
    • INTERSECT: return only rows present in both query results (available in MySQL 8.0.31+).
    • EXCEPT: Returns rows from the first query result that are not present in the second (available in MySQL 8.0.31+).

  • Files

    Select can be used in combination with reading or writing files.

    • Reading files

      To read a file it is required that the user has FILE privilege.

      To read a file in a select statement use

      SELECT LOAD_FILE('<filename>');

    • Writing files

      To write a file it is required that:

      • the user has FILE privilege.
      • MySQL global variable secure_file_priv, which sets read/write folder, is not NULL. If it is empty read/write is allowed on the entire system.
      • the user has write-access to the location that the file should be written to.

      Writing the result of a select statement to a file is done with:

      <select_statement> INTO OUTFILE '<filename>';

Update

The update statement can be used to update records:

UPDATE <table name> SET attr1=newvalue1, attr2=newvalue2,... [WHERE <condition>];

Delete

Records can be deleted with:

DELETE FROM <table name> [WHERE <condition>]

Conditions

Used in WHERE, HAVING, and ON clauses to filter data.

They can be categorised as follows:

  • Comparison Operators: =, <>, !=, <, <=, >, >=.
  • Pattern Matching: REGEXP (Regular Expressions), LIKE <pattern> where pattern holds a string and % for any-char wildcard and/or _ for single-char wildcard).
  • Logical Operators: AND (&&), OR (||), NOT (!), XOR. Returns 1 for true and 0 for false.
  • Range and Set Operators: BETWEEN, IN.
  • Null Checks: IS NULL, IS NOT NULL.
  • Existence Checks: EXISTS.

Comments

Can be made with:

  • --: following a comment that will not be executed
  • /* <comment> */: in-line comment.

Fingerprinting

If a SQL Injection is possible, then MySQL can be identified by the following payloads:

  • SELECT @@version: will print version if MySQL, will throw error in other DBMS. Usable when we get the output printed.
  • SELECT POW(1,1): will print 1 if MySQL, will throw error in other DBMS. Useful if only numeric output is printed.
  • SELECT SLEEP(5): will delay page response time for 5 seconds and return 0, will not delay response time in other DBMS. Useful when for Blind SQL Injections when no output is printed.

INFORMATION_SCHEMA Database

This database contains metadata about the databases and tables on the server.

It is very useful to access when performing SQL Injections since knowledge about databases and tables are often needed to construct payloads.

Useful tables in this database are:

  • SCHEMATA: contain info on databases. The SCHEME_NAME column contains all the database names.
  • TABLES: contain info on tables. The TABLE_NAME column contains table names and the TABLE_SCHEMA column points to the database that each table belong to.
  • COLUMNS: contain info on all columns in the database. The COLUMN_NAME column contains name of all columns, the TABLE_NAME column contains the table containing the column in COLUMN_NAME, and the TABLE_SCHEMA column contain the name of the database containing the column in COLUMN_NAME.
  • USER_PRIVILEGES: contain info on privileges of users.
  • GLOBAL_VARIABLES: contain info on global variables. Useful columns are VARIABLE_NAME and VARIABLE_VALUE.

Graph View

Backlinks