☑️Introduction
Most modern dynamic applications use a database to store and handle data in the back end and queries are used to communicate with the database. When user-supplied information is used to construct the query to the database, malicious users can trick the query into being used for something other than what the original programmer intended, providing the user access to query the database using an attack known as SQL injection (SQLi). Here we focus on MySQL relational database.
Database Management Systems
There are many different types of databases, each of which fits a particular type of use. Traditionally, an application used file-based databases, which was very slow with the increase in size. This led to the adoption of Database Management Systems (DBMS).
A Database Management System (DBMS) helps create, define, host, and manage databases. Various kinds of DBMS were designed over time, such as file-based, Relational DBMS (RDBMS), NoSQL, Graph based, and Key/Value stores.
Some of the essential features of a DBMS include:
Feature
Description
Concurrency
A real-world application might have multiple users interacting with it simultaneously. A DBMS makes sure that these concurrent interactions succeed without corrupting or losing any data.
Consistency
With so many concurrent interactions, the DBMS needs to ensure that the data remains consistent and valid throughout the database.
Security
DBMS provides fine-grained security controls through user authentication and permissions. This will prevent unauthorized viewing or editing of sensitive data.
Reliability
It is easy to backup databases and rolls them back to a previous state in case of data loss or a breach.
Structured Query Language
SQL simplifies user interaction with the database with an intuitive syntax supporting various operations.
Here is a 3-tier architecture:
It is possible to host the application server as well as the DBMS on the same host. However, databases with large amounts of data supporting many users are typically hosted separately to improve performance and scalability.
Types of Databases
Databases, in general, are categorized into Relational Databases and Non-Relational Databases. Only Relational Databases utilize SQL, while Non-Relational databases utilize a variety of methods for communications.
Relational Databases
A relational database is the most common type of database. It uses a schema, a template, to dictate the data structure stored in the database. For example, we can imagine a company that sells products to its customers having some form of stored knowledge about where those products go, to whom, and in what quantity. However, this is often done in the back-end and without obvious informing in the front-end. Different types of relational databases can be used for each approach. For example, the first table can store and display basic customer information, the second the number of products sold and their cost, and the third table to enumerate who bought those products and with what payment data.
Tables in a relational database are associated with keys that provide a quick database summary or access to the specific row or column when specific data needs to be reviewed. These tables, also called entities, are all related to each other. For example, the customer information table can provide each customer with a specific ID that can indicate everything we need to know about that customer, such as an address, name, and contact information. Also, the product description table can assign a specific ID to each product. The table that stores all orders would only need to record these IDs and their quantity. Any change in these tables will affect all of them but predictably and systematically.
However, when processing an integrated database, a concept is required to link one table to another using its key, called a relational database management system (RDBMS). For example, we can have a users table in a relational database containing columns like id, username, first_name, last_name, and others. The id can be used as the table key. Another table, posts, may contain posts made by all users, with columns like id, user_id, date, content, and so on.
We can link the id from the users table to the user_id in the posts table to retrieve the user details for each post without storing all user details with each post. A table can have more than one key, as another column can be used as a key to link with another table. So, for example, the id column can be used as a key to link the posts table to another table containing comments, each of which belongs to a particular post, and so on.
The relationship between tables within a database is called a Schema.
Non-Relational Databases
A non-relational database (also called a NoSQL database) does not use tables, rows, and columns or prime keys, relationships, or schemas. Instead, a NoSQL database stores data using various storage models, depending on the type of data stored. Due to the lack of a defined structure for the database, NoSQL databases are very scalable and flexible. Therefore, when dealing with datasets that are not very well defined and structured, a NoSQL database would be the best choice for storing such data. There are four common storage models for NoSQL databases:
Key-Value
Document-Based
Wide-Column
Graph
Each of the above models has a different way of storing data. For example, the Key-Value model usually stores data in JSON or XML, and have a key for each pair, and stores all of its data as its value:
The above image can also be represented as JSON:
It looks similar to a dictionary item in languages like Python or PHP (i.e. {'key':'value'}), where the key is usually a string, and the value can be a string, dictionary, or any class object.
The most common example of a NoSQL database is MongoDB.
Non-relational Databases have a different method for injection, known as NoSQL injections. SQL injections are completely different than NoSQL injections.
Last updated