Security Testing – SQL Basics

• SQL stands for Structured Query Language.
• Using SQL, we can access the database and perform various operations like retrieving and modifying the data.
• The following things can be performed in the Application Database using SQL:
  • Retrieving the data from a Database
  • Inserting new data into a Database
  • Updating the data in a Database
  • Deleting the records in a Database
  • Creating new Databases
  • Creating new tables inside the Database
  • And many more.
• Data in the Database will be stored in the form of tables, where each and every table will organize the data in the form of columns and rows.

 
Select * from Table_Name : To retrieve data from all the columns in the specified table.

Step#1: Click here to visit an online tool provided by W3Schools for practicing SQL online

Step#2: View the list of tables available in their database as shown below and choose the first table say Customers table.

Step#3: Type the query ‘SELECT * from Customers‘ into the SQL Statement field to retrieve all the data stored in the Customers table and click on ‘Run SQL >>’ button to execute as shown below:

Step#4: Observe that all the Customers table data got retrieved and displayed on the page as shown below:

Step#5: Similarly practice retrieving all data from the other tables specified under the Step 2 i.e. Categories, Employees, OrderDetails, Orders, Products, Shippers, and Suppliers.

Select column1, column2,.. from Table_Name : To retrieve data only from the specified columns in the specified table.

Step#1: Click here to visit an online tool provided by W3Schools for practicing SQL online

Step#2: Type the query ‘SELECT CustomerName, City from Customers‘ into the SQL Statement field to retrieve all the data stored in the Customers table and click on ‘Run SQL >>’ button to execute.

Step#3: Observe that only the data under CustomerName and City columns of Customers table got retrieved and displayed on the page as shown below:

Step#4: Similarly practice retrieving data from specified columns from the other tables.

Select Column from Table_Name where Column=’TextValue’ : As shown in the below diagram, only the data under CustomerName belonging to the City London will be retrieved and displayed. Here where clause will filter the data to be retrieved using the specified city. i.e. In the below example, the customer names belonging to the city London in the Customers table will be displayed. Column=’TextValue’ – Observe that the text value should be surrounded by ”. Column=NumericValue – It’s not required to surround the Number value by ”. 

Where clause and possible operators: In the above examples, we have only used the = operator, but we can use any of the following operators (Practice here) :

• • =  (Equal to Operator): SELECT CustomerName FROM Customers WHERE City=’London’ 
  • < (Less than Operator) : SELECT CustomerName, CustomerID FROM Customers where CustomerID < 4
  • > (Greater than Operator) : SELECT CustomerName, CustomerID FROM Customers where CustomerID > 87
  • <= (Less than or Equal to Operator) : SELECT CustomerName, CustomerID FROM Customers where CustomerID <= 4
  • >= (Greater than or Equal to Operator) : SELECT CustomerName, CustomerID FROM Customers where CustomerID >= 90
  • <> (Not Equal to Operator) : SELECT CustomerName, Country FROM Customers where Country <> ‘Mexico’
  • Between Operator:
    • Between Numbers – SELECT CustomerName, CustomerID FROM Customers where CustomerID between 5 and 10  
    • Between Dates – SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#
  • Like Operator:
    • Customer Names starting with B – SELECT CustomerName FROM Customers where CustomerName like ‘B%’  
      • Customer Names ending with B – SELECT CustomerName FROM Customers where CustomerName like ‘%B’ 
    • Customer Names having Z letter in between – SELECT CustomerName FROM Customers where CustomerName like ‘%Z%’
    • Customer Names having roc letters in between – SELECT CustomerName FROM Customers where CustomerName like ‘%roc%’
    • Customer Names having C letter in the second position -SELECT CustomerName FROM Customers where CustomerName like ‘_E%’
  • NOT Like Operator: Does the opposite of Like operator
    • Customer Names not starting with A – SELECT * from Customers where CustomerName Not Like ‘A%’
  • In operator: Customers in the provided Countries list – SELECT CustomerName, Country FROM Customers where Country In (‘USA’,’UK’)
  • And Operator: Retrieving the customer name who is living in the specified city of the specified country – SELECT CustomerName, City FROM Customers where Country=’USA’ And City=’Seattle’
  • Or Operator: Retrieving the customer names who are living in the specified cities – SELECT CustomerName, City FROM Customers where City=’London’ or City=’Seattle’
  • NOT operator: Retrieving the customer names who are not living in Seattle – SELECT CustomerName, City FROM Customers where NOT City=’Seattle’

• Order By Keyword: Is used to sort the results in ascending or descending order. The following are few examples (Practice here) :

• • All the data in the table will be retrieved and displayed in ascending order of the Country – SELECT * FROM Customers order by Country
  • All the data in the table will be retrieved and displayed in descending order of the Country – SELECT * FROM Customers order by Country DESC
  • All the data in the table will be retrieved and displayed in ascending order of the Country – SELECT * FROM Customers order by Country ASC
  • All the data in the table will be retrieved and displayed in ascending order of both Country and City – SELECT * FROM Customers order by Country, City
  • All the data in the table will be retrieved and displayed in ascending order of Country and descending order of City – SELECT * FROM Customers order by Country ASC, City DESC

• Insert Into command is used to add new data into the Database table (Practice here).

• • Inserting a new complete record into the existing table – Insert Into Customers values (92, ‘Arun Motoori’, ‘QAFox’, ‘Silicon Valley’, ‘Hyderabad’, 500081, ‘India’)
  • Inserting a new record with partial columns data into the existing table – Insert Into Customers(CustomerID, CustomerName, ContactName, Country) values (93, ‘Murali Krishna’, ‘QAFox’, ‘India’)

• Null Values: Null value means a cell having no value in the Database table (Practice here).
  • • Is Null and Is Not Null keywords can be used to find the Null values.
    • Is Null keyword: Find the records having null values in the Address column – Select * from Customers where Address Is Null
    • Is Not Null keyword: Find the records having values in the Address column – Select * from Customers where Address Is Not Null

  ---

  • Update command is used to modify the existing records in a Database table (Practice here).
  • • Update the CustomerName and City in the Customers table using the CustomerId – Update Customers Set CustomerName=’Arun Motoori’, City=’Hyderabad’ where CustomerId=1
    • Updating the ContactName of multiple records of the Customers who are from Mexico Country – Update Customers Set ContactName=’QAFox’ where Country=’Mexico’

  ---

  • Delete command is used for deleting the existing records in a Database table (Practice here).
  • • Delete Customer details from Customers table whose CustomerId is 2 – Delete from Customers where CustomerID=2
    • To delete the complete table, we can use – Delete * from Customers
    • To delete the complete table, we can also use – Delete from Customers

  ---

  • Top Clause: This keyword is used to retrieve only top few records, with an intention of improving the performance (Practice here).
  • • Only retrieve and display the Top 5 rows – SELECT TOP 5 * FROM Customers
    • Only retrieve and display the Top 5 % of records – SELECT TOP 5 PERCENT * FROM Customers

  ---

  • Limit Clause: Has same purpose of Top Clause (Practice here).
    • • Only retrieve and display the Top 5 rows – SELECT * FROM Customers LIMIT 5

    ---

    • MIN() AND MAX() functions: Can be used to retrieve the records having minimum and maximum column values (Practice here).
    • • Retrieve the Customer record whose CusotmerID is the least of all the other records – SELECT MIN(CustomerID) FROM Customers
      • Retrieve the Customer record whose CustomerID is the largest of all the other records – SELECT MAX(CustomerID) FROM Customers

    ---

    • COUNT(), AVG() and SUM() functions: Can be used to return the count, average and sum of the specified Column values (Practice here).
    • • Retrieve the count of records – SELECT COUNT(CustomerID) FROM Customers
      • Retrieve the average of CustomerID numbers – SELECT AVG(CustomerID) FROM Customers
      • Retrieve the sum of CustomerID numbers – SELECT SUM(CustomerID) FROM Customers

    ---

    • Wildcards: Wildcard characters like %, _ , [  ], [! ] can be used in the SQL queries (Practice here)
    • • Using % wild card – Filtering the records where the customer name has ‘A’ in between the text – SELECT * from Customers where CustomerName Like ‘%A%’
      • Using _ wild card – SELECT * from Customers where City Like ‘_e_l_n’
      • Using [  ] wild card – Filter the records whose city names start with a or b or c – SELECT * FROM Customers WHERE City LIKE ‘[abc]%’
      • Using [ ] wild card – Filter the records whose city names start with any letter from a to f (i.e. a or b or c or d or e or f) – SELECT * FROM Customers WHERE City LIKE ‘[a-f]%’
      • Using [! ] wild card – Filter the records whose city names not start with a or b or c – SELECT * FROM Customers WHERE City LIKE ‘[!abc]%’

    ---

    • As keyword for Alias: Retrieved records will be displayed under the specified Alias names (Practice here) :
    • • Retrieve the data from CustomerID, CustomerName columns of Customers table but display under the alias names ID, Names in the results – SELECT CustomerID AS ID, CustomerName AS Name FROM Customers
      • Using + operator to concatenate Columns results and displaying these results under a single name using As keyword – SELECT CustomerName + ‘, ‘ + Address + ‘, ‘ + PostalCode + ‘ ‘ + City + ‘, ‘ + Country AS Address FROM Customers

    ---

    SQL Joins: Joins are used to combine two or more tables in a database. The following are the different types of Joins (Practice here) :

    Inner Join, Left Outer Join, Right Join, Full Outer Join and Self Join

  Inner Join: Inner join helps us in selecting and retrieving the records which have common values in both tables. Here in the below example, we are inner joining Orders and Customers tables with their common column values in CustomerID column.

  Select Orders.OrderID, Customers.CustomerName
  From Orders
  Inner Join Customers on Customers.CustomerID=Orders.CustomerID

   
  Left Join: Left Join helps us in selecting and retrieving all the records from the Left side table (i.e. first table) and the matched records from the right table. If there is nothing matching in the right table, a NULL value will be displayed form the right table. Here in the below example, we are left joining Customers  table (left table) and Orders table (right table) to retrieve all the records from the left table and display the common values in the right table.

  Select Customers.CustomerName, Orders.OrderID
  From Customers
  left join Orders on Customers.CustomerID=Orders.CustomerID

   
  Right Join: Right Join helps us in selecting and retrieving all the records from the Right side table (i.e. second table) and the matched records from the left table. If there is nothing matching in the left table, a Null value will be displayed from the left table. Here in the below example, we are right joining Customers table (left table) and Orders table (right table) to retrieve all the records from the right table and display the common values in the left table.

  SELECT Orders.OrderID, Employees.FirstName
  FROM Orders
  RIGHT JOIN Employees
  ON Orders.EmployeeID = Employees.EmployeeID

   
  Full Outer Join: Returns all the records when there is a match in the left table or right table. The following is an example for Full Outer Join.

  SELECT Customers.CustomerName, Orders.OrderID
  FROM Customers
  FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID

   
  Self Join: Self Join is a regular join which helps us in joining the same table with itself. The following is an example of Self Join.

  SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
  FROM Customers A, Customers B
  WHERE A.CustomerID <> B.CustomerID
  AND A.City = B.City

  ---

  Union operator: Union operator is used to combine the results retrievable from two or more Select statements. The following is an example of using Union operator.

  SELECT CustomerID FROM Customers
  UNION
  SELECT CustomerID FROM Orders

  ---

  Group By keyword: Group By is generally used with the functions like COUNT(), MAX(), MIN(), SUM(), AVG(). The following is an example which provides the count of Customers in every Country.

  SELECT COUNT(CustomerID), Country
  FROM Customers
  GROUP BY Country

  ---

  Having Clause: As where clause cannot be used with the functions like COUNT(), MAX(), MIN(), SUM() and AVG(), we need to use Having clause in place of it. The following is an example which provides the countries wise count whose count is greater than 3.

  Select Count(CustomerID), Country
  From Customers
  Group by Country
  Having Count(CustomerID)>3

  ---

  • SQL Comments: SQL comments can be categorized into the single line and multi-line comments.
  • • Single Line Comments: Single line comments in SQL will start with – – and they won’t get executed. The following example will demonstrate the single line comment:
                      – – This is a single-line comment and it won’t get executed
                      Select * from Customers
  • • Multi-line Comments: Multi-line comments in SQL starts with /* and ends with */ . The following example will demonstrate the multi-line comment:
                      /* This is a
                         multi-line
                        comment */
                     Select * from Customers

  ---

  • Create Database keywords: These keywords can be used to create a database in SQL. The following example will create a database named ‘QAFox’ using SQL queries:
    • Create Database QAFox

  ---

  • Drop Database keywords: These keywords can be used to remove/drop a database in SQL. The following example will drop a database named ‘QAFox’ using SQL queries:
    • Drop Database QAFox

  ---

  • Create Table keywords: These keywords can be used to create a new table in the database. The following example will create a table named Employees.
    • Create Table Employees ( EmployeeID int, FirstName varchar(255), LastName varchar(255), City varchar(255) )

  ---

  • Drop Table keywords: These keywords can be used to remove/drop a table from the database. The following example will drop a table named Employees from  Database.
    • Drop Table Employees

  ---

  • Truncate Table keywords: These keywords can be used to clear all the data from the tables. Here, in this case, the table won’t be deleted, instead, the data inside the table will be removed. The following example will clear the data from Employees table.
    • Truncate Table Employees

  ---

  • Alter Table keywords: These keywords can be used to add, delete or modify columns in the table. The following examples will demonstrate how to add new columns, delete existing columns and modify existing columns from a table.
    • Add keyword – Adding new Columna named Age and DateOfBirth into the Employees table –  Alter Table Employees Add Age int, DataOfBirth date
    • Drop keyword – Deleting LastName Column from the Employees table – Alter Table Employees Drop LastName
    • Modify keyword – Modify the data type of DateOfBirth columns in Employees table from date to year – Alter Table Employees Modify DateOfBirth year

  ---

  • SQL Constraints: We can apply rules to the columns while creating a table or while altering a table. The following are the different types of constraints that can be applied to the table columns.
    • NOT NULL – This constraint can be used to specify that the column cannot be Null and is mandatory – Create Table Employees ( EmployeeID int  NOT NULL, FirstName varchar(255), LastName varchar(255) )
    • UNIQUE – This constraint can be used to specify that the column should contain only unique values and duplicate values should not be allowed – Create Table Employees (EmployeeID int UNIQUE, FirstName varchar(255), LastName varchar(255) )
    • PRIMARY KEY – This constraint when used will provide the benefits of both NOT NULL and UNIQUE constraint –  Create Table Employees (EmployeeID int, FirstName varchar(255), LastName varchar(255), PRIMARY KEY (EmployeeID) )
    • FOREIGN KEY – This is constraint using which we can link two tables together by linking field or fields in one table with the PRIMARY KEY in another table. The following example will demonstrate the usage of FOREIGN KEY.
      • Let’s assume PersonID column is the PRIMARY KEY in Persons table
      • And also, OrderID and OrderNumber are two columns in Orders table.
      • The following example will link two tables by making the PRIMARY KEY of Persons table as a FOREIGN KEY of Orders table:
        • Create Table Orders ( OrderID int, OrderNumber int, PersonID int FOREIGN KEY REFERENCES Persons(PersonID) )
    • CHECK – This constraint can be used to limit the range of values which can be entered into a specific column. The following example uses CHECK constraint in restricting the age to be equal to or above 18 years.
      • Create Table Persons ( PersonID int, FirstName varchar(255), Age int, CHECK(Age<=18) )
    • DEFAULT – This constraint can be used to specify a default value that can be shown in the specific column of the table when no value is provided. The following example uses DEFAULT constraint for City Column in Persons table, if no City is specified while entering the data into Table, ‘Hyderabad’ will be considered as a default value.
      • Create Table Persons( PersonID int, FirstName varchar(255), City varchar(255) DEFAULT ‘Hyderabad’ )

  ---

  Conclusion: Understanding SQL basics is very much required for mastering Security Testing > SQL Injections concepts. All the basic concepts of SQL provided in this post needs to be understood and learned by the Security Testing enthusiasts to identify the vulnerabilities around SQL Injections.
  Please leave your questions/comments/feedback below.
  Happy Learning 🙂
  Arun Motoori (www.QAFox.com)