learn_sql_queries

Learn SQL Queries

In this article you will learn about SQL queries. It is very useful to understand the basics of SQL so that you will be more familiar with the commands when trying to exploit SQL injection vulnerabilities.

First of all, please log into your database, for the purpose of this demonstration I will be using Metaspoitable’s Owasp10 database which you can download from Rapid7. 

mysql -u root -h 192.168.19.132

Once logged in we want to show the databases show databases; and show tables show tables;

SQL_show databases
show databases;
MySQL_show_tables
show tables;

SELECT statement

To see all the information on data table, we need to use the SELECT statement. It is not neccesary to capitalise key words and functions but it does make it easier to read the commands. An asterix * represents all. The query ends with a semi-colon.

To see all the infomation in the table for accounts, we can use the asterix. As you can see in the screenshot all columns and rows on the table are displayed.

SELECT * FROM accounts;

DatabaseTable_Show_All
SELECT * FROM accounts;
The select all statement is fine for small tables of data but if you have very large tables of data you may want to be more specific with your query. Lets look at how we can retrieve only specific columns.
SELECT username FROM accounts;
SELECT password FROM accounts;
SELECT username, password FROM accounts;
SELECT username FROM accounts
SELECT username FROM accounts;
SELECT password FROM accounts
SELECT password FROM accounts;
SELECT username password from accounts
SELECT username password from accounts;

WHERE clause

The WHERE condition will help us to filter the data for queries by reading each row and checking if the value matches the query. This is especially useful when there are too many rows of data to read through.
Syntax:
SELECT column, column FROM table
WHERE condition AND/OR condition;


To demonstrate I have chosen the pen_test_tools table on the Owasp10 database as it has more information that can be filtered. We will look at text queries now and then numeric queries in the next section.All text strings must be in quotation marks so that SQL can separate condition words from key words. Lets have a look at some examples.

Find all the Scanner types of pentest tools: SELECT * FROM pen_test_tools WHERE tool_type = "Scanner";

Find tools with a name like Burp Suite: SELECT tool_name, tool_type FROM pen_test_tools WHERE tool_name LIKE "burp%suite";

Find tool types that are not scanners: SELECT tool_name, tool_type FROM pen_test_tools WHERE tool_type != "Scanner";

WHERE type
WHERE tool_type = "Scanner";
WHERE type not scanner
WHERE type not scanner
WHERE tool_name like burpsuite
WHERE tool_name LIKE "burp%suite";
String Operators and Conditions
= Exact string match, case sensitive
LIKE Exact string match, case insensitive
IN or NOT IN Select values in a list or not in a list
!= or <> Exact string unequal match, case sensitive
NOT LIKE Exact string unequal match, case insensitive
_ Used in a string to match a single character (with LIKE or NOT LIKE)
% Used in a string to match of zero or more characters (with LIKE or NOT LIKE)
WHERE clause with numeric queries
We can also filter the data for queries with numeric values.
SELECT ccid, ccnumber, ccv, expiration FROM credit_cards WHERE ccid = 3;
SELECT ccid, ccnumber, ccv, expiration FROM credit_cards WHERE ccid BETWEEN 1 AND 3;
SELECT ccid, ccnumber, ccv, expiration FROM credit_cards WHERE ccid NOT BETWEEN 1 AND 3;
WHERE ccid
WHERE ccid = 3;
NUMERIC BETWEEN
NOT BETWEEN 1 AND 3;
Numeric Operators and Conditions
= != < > <= >= Regular numeric operators
IN or NOT IN Select values in a list or not in a list
BETWEEN AND Numbers between a range of 2 values
NOT BETWEEN AND Numbers are not between a range of 2 values
Ordering and limiting the results
Most databases will not have any kind order but SQL allows us to sort alpha-numerically the results ascending or descending, with the ORDER BY clause.
SELECT * FROM accounts ORDER BY username ASC;
SELECT * FROM accounts ORDER BY username DESC;
Furthermore, incase there are thousands or millions of results to read, SQL also allows us to limit the results. As you can see in the screenshot, LIMIT has limited the results to 8. The OFFSET clause starts counting from a specified row, in this example the OFFSET is 2 and so usernames admin and adrian have been removed from the results.
SELECT * FROM accounts ORDER BY username ASC LIMIT 8 OFFSET 2;
ORDER BY username ASC
ORDER BY username ASC;
ORDER BY username DESC
ORDER BY username DESC;
LIMIT OFFSET
LIMIT 8 OFFSET 2;
NULL Queries

Try to avoid have NULL values in your database by replacing them with appropriate data values such as empty strings for text data and 0 for numeric data. However when it is not possible to have columns with complete values we can filter the results to show colums with NULL values or NOT NULL values.
SELECT * FROM tiki_modules WHERE title IS NULL;
SELECT * FROM tiki_modules WHERE title IS NOT NULL;

OR 1=1

1=1 is True. As you can see in the screenshot, the query for username = 17 returned no results. But when adding OR 1=1 to the statement, all the rows of the table are returned because the statement is always True.
SELECT * FROM accounts WHERE username = 17;
SELECT * FROM accounts WHERE username = 17 OR 1=1;

WHERE title is NULL
WHERE title is NULL;
or 11
OR 1=1;

I hope this article helped you learn SQL queries. I recommend reading the next article Learn the basics of SQL injection. Please follow me on Twitter for more articles and updates.