Cyber Reference

sql-basics

Properties2
tagsexploit, sql
aliasesSQL Basics

3 min read

Most modern web applications utilized a database structure on the back-end. The web application’s back end will issue queries to the database to build the response. It is often possible to trick the database query into being used for something other than its original intent.

Relational Databases

Most common. Uses a schema, a template, to dictate the structure stored in the database. Tables in relational database are associated with keys that provide a quick database summary or access to the specific row or column. Tables are related to each other. A relational database management system (RDBMS) helps link one table another using its key. The relationship between tables within a database is called a Schema.

Non-relational Databases

Does not use tables, rows, and columns. Uses storage model depending on the type of data stored. For instance, the key-value model can be used and stores data in JOSN or XML.

MySQL

By default operated on port 3306

# authenticate to mySQL/MariaDB in localhost
mysql -u root -p
 
# authenticate to other host
mysql -u root -h docker.hackthebox.eu -P 3306 -p

SQL Syntax

CREATE DATABASE users;
 
SHOW DATABASES;
 
-- Select a DB
USE users;
 
-- DBMS stores data in the form of tables. 
CREATE TABLE logins (
    id INT,
    username VARCHAR(100),
    password VARCHAR(100),
    date_of_joining DATETIME
    );
 
-- 
SHOW TABLES;
 
-- list the table structure with its field and data types
DESCRIBE logins;

Properties can be set for the table and each column. Some important properties include

  • AUTO_INCREMENT
  • NOT NULL requires the column never to be empty.
  • UNIQUE item is always unique.
  • DEFAULT specify the default value.
  • PRIMARY KEY uniquely identify each in the table.
-- add new records to a given table
INSERT INTO table_name VALUES (column1_value, column2_value, column3_value, ...);
 
-- retrieve data
SELECT * FROM table_name;
 
-- specific columns
SELECT column1, column2 FROM table_name;
 
-- remove tables and databases
DROP TABLE logins;
 
-- change name of any table and any of its fields
ALTER TABLE logins ADD newColumn INT;
 
-- change a table's properties
UPDATE table_name SET column1=newvalue1, column2=newvalue2, ... WHERE <condition>;

We can also control the results output of any query.

-- sort in descending order
SELECT * FROM logins ORDER BY password;
 
-- LIMIT results to number of records
SELECT * FROM logins LIMIT 2;
 
-- filter for specific data
SELECT * FROM table_name WHERE <condition>;
 
-- select records matching a certain pattern
SELECT * FROM logins WHERE username LIKE 'admin%';

We may need more than a single condition.

-- 
condition1 and condition2
 
-- or operator
SELECT 1 = 1 OR 'test' = 'abc';
 
-- 
SELECT NOT 1 = 1;
 
-- all of these have symbol equivalents
-- AND is &&
-- OR is ||
-- NOT is !=

Graph View