SQL

What is SQL?

SQL stands for Structured Query Language and is a programming language that is used to manage relational databases such as Microsoft SQL also known as MSSQL, MySQL, MariaDB, and PostgreSQL. These types of databases are used worldwide in combination with applications or web applications and knowing how to do some of the basic queries may help you in the long run of exploiting your first SQL injection vulnerability for example.

Different types of databases

To keep the long story short there are generally three types of databases:

• Relational Databases
• Flat Databases
• NoSQL

Relational Databases

What are they?

A type of database in which data is stored in multiple, related tables. Within those tables, the data is stored in rows and columns and all use SQL queries to update, create, and delete records in the table and work well with structured data.

Use cases:

You would use a relational database when you have an application that creates structured data. It is most commonly used with Web Applications and Desktop Applications that require querying a specific table to provide the user with data

Examples:

• Microsoft SQL Server
• Oracle Database
• MySQL
• PostgreSQL
• IBM Db2

Flat Databases

What are they?

This is a type of database that doesn’t offer any layer of security other than the sole control of the location of where the file is placed. There’s not much to say about it other than if you want to design or build an application this is exactly what you don’t need.

Use cases:

You would create a flat database as part of the output of a script or as part of an import into a different application. Or you would use such a type of database to store your most sensitive information such as passwords and emails (please don’t do this for the love of god. There’s a special place in hell for people that save passwords like this).

Examples:

• Excel files
• CSV files

NoSQL

What are they?

A NoSQL database is exactly the opposite of relational databases, it is a type of SQL-like database that stores the data in a different format than relational databases. They usually are known mostly for their flexible schemas, ease of use (developer-wise), and speedy queries due to the data model and horizontal scaling.

Use cases:

You would use a NoSQL database when you want to store for example a Web application where you would store multiple pieces of information related to one item do example an application for Inventory and Catalogue Management, like storing the details about books or assets that you own or manage.

Examples:

• MongoDB
• Redis
• Cassandra

Where are they used?

Literally, everywhere. Every application, web or local, that stores some sort of data from the user, stores it somewhere which could be a SQL or NoSQL database or any of the less-known types of databases that are more or less associated with the two types mentioned above.

Why is it helpful?

If you want to get down the security path knowing the basic elements of databases will help you along the way through your journey and even be able to just delve into trying some stuff such as:

CURRENT_USER -- Gets you the current user in MSSQL
SELECT TOP 1 * FROM SCHEMA.TABLE -- Gets the top 1 record from a specific schema and table

Practical examples

• Codecademy - SQL module
• Hackerrank - SQL Challenges