What is a Database?
According to Oracle, “A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database. Data within the most common types of databases in operation today is typically modeled in rows and columns in a series of tables to make processing and data querying efficient. The data can then be easily accessed, managed, modified, updated, controlled, and organized. Most databases use structured query language (SQL) for writing and querying data” When it comes to web applications, the primary role of a database is to store the persistent data so that the application can retrieve whenever it needs the stored data. and display updated information in a web application. Starting from a small hotel to a huge ecommerce business, any website with dynamic functionality mostly relies on a robust backend database for their data storage needs. MySQL, MSSQL, Oracle and PostgreSQL are some of the most commonly used database management systems.
Web application Architecture
Let us consider a dynamic website with a backend database and understand how typical login workflow happens at a high level. A user enters credentials into the website’s login form and the website receives the credentials entered into the form. These credentials will then be sent to the server for further processing. The server will then form a SQL query dynamically based on the user input and executes it against the database. If the details entered by the user are found in the database, the user will be logged in. Otherwise, the website may show an “invalid login” message.
Structure Query Language (SQL)
Most databases use structured query language (SQL) for writing and querying data and web applications include these queries in their server side components to be able to interact with the backend databases. For example a website developed using HTML, CSS and PHP includes the SQL queries in its PHP files on the server. It is commonly seen that PHP web applications use MySQL, ASP.NET based web applications use MSSQL and Java based applications use Oracle as their backend databases. While this is a commonly seen pattern, it doesn’t mean that applications should strictly follow this pattern. Now let us consider an example where applications communicate with databases. Let us assume that there is an E- Commerce website, which contains features like displaying various products and adding products to the cart, and creating new categories of products. All these features require interaction with the database and can be implemented using simple SQL queries as follows.
Creating a new category of products in database
During the initial database setup, a new table would be created and required attributes to store the details of the new category of products would be given as follows. Model_name Varchar (50); Price int (10); ); We used the SQL statement CREATE TABLE. We are creating a new table named Mobiles in the database and we gave few attributes to all the products in this table.
Inserting data into a table
Once the table is created, the web application will be able to insert data into the table dynamically when the users are using the application. Features such as add to cart may require the application to store the information in a database. This can be achieved using the following SQL Query.
Retrieving Data from table
Let us consider a scenario where users apply filters on the products they are viewing. The data should be retrieved from the database based on the user filter and the data should be displayed appropriately. This operation can be achieved using a SELECT query as shown below. Here we are selecting all the rows of Brand and Model_name columns, where the price is less than 400 bucks. Once the data is retrieved by the application, it can be displayed on the website. These are some of the commonly used examples of the SQL queries.
Security risks associated with SQL Queries
When developers make use of SQL Queries in web applications, there is a possibility of introducing a security vulnerability known as SQL Injection. This happens when user supplied input is used to construct dynamic SQL Queries without sufficient input validation. Let us consider the following example to better understand this. The following URL of the vulnerable website is used to display the product with the id 1. Let us assume that the value of pid is passed to the following query to be able retrieve the product details based on the product id. A malicious actor can enter the following into the URL to be able to append and execute user controlled SQL Queries by using the web application. This will form the following query in the background and it will be executed by the database. Clearly, the user of the web application is able to manipulate the backend queries using SQL Injection, which is otherwise not possible. This can lead to leaking the entire database, which may include customer data and administrative user credentials. In certain scenarios, it is also possible to achieve full access to the underlying server. We will discuss more details about SQL Injection vulnerabilities in the upcoming articles.
Conclusion
Databases are widely used in web applications and other software alike. They add great benefits to applications, which include storing persistent data, providing better user experience by allowing the user to retrieve dynamic data based on user supplied filters aside from others. While this is great, developers must be aware of the security risks dynamic websites bring due to the use of databases. In the next few articles, we will discuss more SQL Injection related concepts such as their causes, exploitation techniques and mitigations.
Sources
https://owasp.org/www-community/attacks/SQL_Injection https://www.oracle.com/sg/database/what-is-database.html https://owasp.org/www-project-top-ten/2017/A1_2017-Injection.html
