Adam's SQL Notes for crazy people:



Four Basic SQL Operations, or CRUD

Creating Data – Filling data into tables.
Reading Data – Query data out of a table.
Updating Data – Change data already in a table.
Deleting Data – Remove data from the table.

"CRUD” is considered the fundamental set of four basic functions or features that every database must have.

These are the basic features that use these important SQL commands: `CREATE`, `INSERT`, `SELECT`, `UPDATE`, `DELETE`, and `DROP`.


SQL Data Types

Here’s a list of all the formal data types that ISO/IEC standard SQL recognizes. In addition to these, you may define additional data types that are derived from these.

Exact Numerics:

    INTEGER
    SMALLINT
    BIGINT
    NUMERIC
    DECIMAL

Approximate Numerics:

    REAL
    DOUBLE PRECISION
    FLOAT

Binary Strings:

    BINARY
    BINARY VARYING
    BINARY LARGE OBJECT

Boolean:

    BOOLEAN

Character Strings:

    CHARACTER
    CHARACTER VARYING (VARCHAR)
    CHARACTER LARGE OBJECT
    NATIONAL CHARACTER
    NATIONAL CHARACTER VARYING
    NATIONAL CHARACTER LARGE OBJECT

Datetimes:

    DATE
    TIME WITHOUT TIMEZONE
    TIMESTAMP WITHOUT TIMEZONE
    TIME WITH TIMEZONE
    TIMESTAMP WITH TIMEZONE

Intervals:

    INTERVAL DAY
    INTERVAL YEAR

Collection Types:

    ARRAY
    MULTISET

Other Types:

    ROW
    XML





SQL Value Functions

These SQL value functions perform operations on data. There are all kinds of operations that could conceivably be performed on data items, but these are some that are needed most often.

String Value Functions

Function 			Effect
-------------------------------
SUBSTRING 			Extracts a substring from a source string

SUBSTRING SIMILAR 	Extracts a substring from a source string, using POSIX-based
					regular expressions

SUBSTRING_REGEX 	Extracts from a string the first occurrence of an XQuery
					regular expression pattern and returns one occurrence of the
					matching substring

TRANSLATE_REGEX 	Extracts from a string the first or every occurrence of an
					XQuery regular expression pattern and replaces it or them with an
					XQuery replacement string

UPPER 				Converts a character string to all uppercase

LOWER 				Converts a character string to all lowercase

TRIM 				Trims off leading or trailing blanks

TRANSLATE 			Transforms a source string from one character set to
					another

CONVERT 			Transforms a source string from one character set to
					another


Numeric Value Functions

Function 	Effect
-------------------------------
POSITION 			Returns the starting position of a target string within a
					source string
CHARACTER_LENGTH 	Returns the number of characters in a string

OCTET_LENGTH 		Returns the number of octets (bytes) in a character string

EXTRACT 			Extracts a single field from a datetime or interval


Datetime Value Functions

Function 	Effect
-------------------------------
CURRENT_DATE	 		Returns the current date

CURRENT_TIME(p) 		Returns the current time; (p) is precision of seconds

CURRENT_TIMESTAMP(p) 	Returns the current date and the current time; (p) is precision
						of seconds



SQL Set Functions
-------------------------------
The SQL set functions give you a quick answer to questions you may have about the characteristics of your data as a whole. How many rows does a table have? What is the highest value in the table? What is the lowest? These are the kinds of questions that the SQL set functions can answer for you.

COUNT 		Returns the number of rows in the specified table

MAX 		Returns the maximum value that occurs in the specified
			table

MIN 		Returns the minimum value that occurs in the specified
			table
SUM 		Adds up the values in a specified column

AVG 		Returns the average of all the values in the specified
			column


SQL WHERE Clause Predicates
------------------------------
Predicates boil down to either a TRUE or a FALSE result. You can filter out unwanted rows from the result of an SQL query by applying a WHERE clause whose predicate excludes the unwanted rows.

Comparison Predicates

=	 	Equal
<> 		Not equal
< 		Less than
<= 		Less than or equal
> 		Greater than
>= 		Greater than or equal

Other Predicates

ALL 		BETWEEN
DISTINCT 	EXISTS
IN 			LIKE
MATCH 		NOT IN
NOT LIKE 	NULL
OVERLAPS 	SIMILAR
SOME, ANY 	UNIQUE




Introduction

Nowadays, web applications are common around the world. Nearly every major company or organization has a web presence. Maximum of these companies and organizations use web applications to provide various services to users. Some of these web applications employ database driven content. The back-end database often contains confidential and sensitive information such security numbers, credit card number, financial data, medical data. Typically the web user supplies information, such as a username and password and web applications receive user request and interact with the back-end database and returned relevant data to the user.
What is SQL injection?

SQL injection is a technique (like other web attack mechanisms) to attack data driven applications. This attack can bypass a firewall and can affect a fully patched system. The attacker takes the advantage of poorly filtered or not correctly escaped characters embedded in SQL statements into parsing variable data from user input. The attacker injects arbitrary data, most often a database query, into a string that’s eventually executed by the database through a web application (e.g. a login form).


Through SQL Injection attacker can obtain unauthorized access to a database and can create, read, update, alter, or delete data stored in the back-end database. Currently, almost all SQL databases such as Oracle, MySQL, PostgreSQL, MSSQL Server, MS Access are potentially vulnerable to SQL injection attacks. In its most common form, a SQL injection attack gives access to sensitive information such as social security numbers, credit card number or other financial data.





Why SQL injection?

- Identify injectable parameters.
- Identify the database type and version.
- Discover database schema.
- Extracting data.
- Insert, modify or delete data.
- Denial of service to authorized users by locking or deleting tables.
- Bypassing authentication.
- Privilege escalation.
- Execute remote commands by calling stored functions within the DBMS which are reserved for administrators.





SQL injection method

Here are some methods through which SQL statements are injected into vulnerable systems
- Injected through user input.
- Injection through cookie fields contains attack strings.
- Injection through Server Variables.
- Second-Order Injection where hidden statements to be executed at another time by another function.






Vulnerabilities

In computer security, a vulnerability is a weakness which allows an attacker to reduce a system's information assurance. Web-based forms allow some access to the back-end database to allow entry of data and a response, this kind of attack bypasses firewalls and endpoint defenses. Any web form, even a simple logon form or search box (where a user can input data), might provide access to back-end database by means of SQL injection.
vulnerabilities SQL injection

The common reason that an application is vulnerable to SQL injection is improper filtering and lack of validation for user input. Input forms are quite common to collect data from a user. So, practically it is not feasible to close all the entry points to bar SQL injection. To prevent attacks developers must apply proper filtration/validation on all forms.
Vulnerable Applications

– Almost all SQL databases are potentially vulnerable such as MS SQL Server, DB2, Oracle, PostgreSQL, MySQL, MS Access, Sybase, Informix, etc

– Accessed through applications using :
   ASP, JSP, PHP
   Perl and CGI scripts that access databases
   XML, XSL and XSQL
   JavaScript
   Database specific web applications
   Many other





Types of SQL injection

-- Tautology-based SQL Injection

-- Piggy-backed Queries / Statement Injection

-- Union Query

-- Illegal/Logically Incorrect Queries

-- Inference

-- Stored Procedure Injection






Tautologies

Purpose :

    Identify injectable parameters
    Bypass authentication
    Extract data

In logic, a tautology (from the Greek word ταυτολογία) is a formula which is true in every possible interpretation. In a tautology-based attack, the code is injected using the conditional OR operator such that the query always evaluates to TRUE. Tautology-based SQL injection attacks are usually bypass user authentication and extract data by inserting a tautology in the WHERE clause of a SQL query. The query transform the original condition into a tautology, causes all the rows in the database table are open to an unauthorized user. A typical SQL tautology has the form "or <comparison expression>", where the comparison expression uses one or more relational operators to compare operands and generate an always true condition. If an unauthorized user input user id as abcd and password as anything' or 'x'='x then the resulting query will be:

select * from user_details where userid = 'abcd' and password = 'anything' or 'x'='x'




Piggy-backed Queries / Statement Injection

Purpose :

    Extract data
    Modify dataset
    Execute remote commands
    Denial of service

This type of attack is different than others because the hacker injects additional queries to the original query, as a result the database receives multiple SQL queries. The first query is valid and executed normally, the subsequent queries are the injected queries, which are executed in addition to the first. Due to misconfiguration, a system is vulnerable to piggy-backed queries and allows multiple statements in one query. Let an attacker inputs abcd as usrerid and '; drop table xyz -- as password in the login form :
Piggy-backed Queries

Then the application will generate the following query :

select * from user_details where userid = 'abcd' and password = ''; drop table xyz -- '

After completing the first query ( returned an empty result set (i.e. zero rows)), the database would recognize the query delimiter(";") and execute the injected second query. The result of executing the second query would be to drop table xyz, which would destroy valuable information.






Union Query

Purpose:

    Bypassing authentication
    Extract data

This type of attack can be done by inserting a UNION query into a vulnerable parameter which returns a dataset that is the union of the result of the original first query and the results of the injected query.

The SQL UNION operator combines the results of two or more queries and makes a result set which includes fetched rows from the participating queries in the UNION.

Basic rules for combining two or more queries using UNION:

1) A number of columns and order of columns of all queries must be same.

2) The data types of the columns on involving table in each query should be same or compatible.

3) Usually returned column names are taken from the first query.

By default the UNION behaves like UNION [DISTINCT] , i.e. eliminated the duplicate rows; however, using ALL keyword with UNION returns all rows, including duplicates.
The attacker who tries to use this method must have solid knowledge of DB schema.

The two dashes (--) comments out the rest of the query i.e. ' and password = 'abcd'. Therefore, the query becomes the union of two SELECT queries. The first SELECT query returns a null set because there is no matching record in the table user_details. The second query returns all the data from the table emp_details. Let try it with our login form.
union login






Illegal/Logically Incorrect Queries

Purpose :

    Identify injectable parameters
    Identify database
    Extract data

In this type of injection an attacker is trying gather information about the type and structure of the back-end database of a Web application. The attack is considered as a preliminary step for further attacks. If an incorrect query is sent to a database, some application servers return the default error message and the attacker takes the advantage of this weakness. They inject code in vulnerable or injectable parameters which creates syntax, type conversion, or logical error. Through type error, one can identify the data types of certain columns. Logical error often exposes the names of tables and columns.






Inference

Purpose :

    Identify injectable parameters
    Identify schema
    Extract data

In this type of injection, the attack is applied on well-secured databases which do not return any usable feedback or descriptive error messages. The attack is normally created in the style of the true false statement. After finding the vulnerable parameter, the attacker injects various conditions (that he wants to know whether they are true or false) through query and carefully observe the situation. If statement evaluates to true, the page continues to function normally. If false, the page behaves significantly different from the normally functioning. This type of injection is called Blind Injection. There is another type of inference attack which is called Time Attack. In this method, an attacker designs a conditional statement and inject through the vulnerable parameter and gather information based on time delays in the response of the database. See the following code:

http://www.example.com/product.php?product_id=100 AND IF(version() like ‘5%’, sleep(15), ‘false’))--

Here an attacker checks whether the system is using a MySQL version is 5.x or not, making the server delay the answer in 15 seconds (the attacker can increase the delay's time).





Stored Procedures

Purpose :

    Privilege escalation
    Denial of service
    Execute remote commands

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary. Typical use for stored procedures includes data validation  or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.
Stored procedures type of SQL injection tries to execute stored procedures present in the database. Most of the database have a standard set of procedures (apart from user defined procedures) that extend the functionality of the database and allow for interaction with the operating system. The attacker initially tries to find the database type with another injection method like illegal/logically incorrect queries. Once an attacker determines which databases is used in backend then he try to execute various procedures through injected code. As the stored procedure are written by developers, therefore these procedures do not make the database vulnerable to SQL injection attacks. Stored procedures can be vulnerable to execute remote commands, privilege escalation, buffer overflows, and even provide administrative access to the operating system.
If an attacker injects ';SHUTDOWN; -- into either the User ID or Password fields then it will generate the following SQL code :

select * from user_details where userid = 'abcd' and password = ''; SHUTDOWN; -- '

The above command causes a database to shut down.



Alternate Encodings

Purpose:

    Evade Detection

In this case, the attacker injected encoded text to bypass defensive coding practices. Attackers have arranged alternate methods of encoding through their injected strings such as using hexadecimal, ASCII,
and Unicode character encoding. Scanning and detection techniques are not fully effective against alternate encodings. See the following example :

SELECT * FROM users WHERE login= '' AND pass=' ';exec(char(Ox73687574646j776e)) '

In the above code the char() function and ASCII hexadecimal encoding have used. The char() function returns the actual character(s) of hexadecimal encoding of character(s). This encoded string is translated into the shutdown command by the database when it is executed.

-----------------------------------------------------------------------------------





Defending against SQL Injection


Researchers and security managers have proposed various defensive methods to fight against SQL injection attack. The root cause of almost every SQL injection is invalid input checking. Here is a list of prevention methods :

    Input Validation
    Input Checking Functions
    Validate Input Sources
    Access Rights
    Configure database error reporting




Input Validation

– Simple input check can prevent many attacks.
– Always validate user input by checking type, size, length, format, and range.
– Test the content of string variables and accept only expected values.
– Reject entries that contain binary data, escape sequences etc. This can help prevent script injection and can protect against some buffer overrun exploits.
– When you are working with XML documents, validate all data against its schema as it is entered.



Input Checking Functions

– Certain characters and character sequences such as ; , --, select, insert and xp_ can be used to perform an SQL injection attack.
– Remove these characters and character sequences from user input which reduces the chance of an injection attack.
– Scan query string for undesirable word like "insert", "update", "delete", "drop" etc. check whether it represent a statement or valid user input.
– Write a function which can handle all of this.


List of the characters which are used to perform an SQL injection attack :
Input character 	Meaning in SQL
------------------------------------
; 					Query delimiter.

' 					Character data string delimiter.

-- 					Comment delimiter.

/* ... */ 			Comment delimiters. The text between /* and */ is not evaluated by the server.

xp_ 				Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.




Validate Input Sources

– There are so many ways to attack a database, therefore the developer should check and authenticate all input sources and disallow unidentified or untrusted users/websites.
Access Rights/User Permissions

– Create "low privileged" accounts for use by applications.
– Never grant instance­level privileges to database  accounts.
– Never grant database­owner or schema­owner privileges  to database accounts.
– Be aware of the permission scheme of your database.
Configure database error reporting

– Some application server's default error reporting often gives away information that is valuable for attackers (table name, field name, etc.).
– The developer should configure the system correctly, therefore this information will never expose to an unauthorized user.

Apart from the above, there are several methods which can prevent from SQL injection.

Reference : http://en.wikipedia.org/wiki/SQL_injection






=============================================================================
SCRAPHEAP: DO NOT INCLUDE IN SLIDES/NOTES YET


Union-Based SQL Injection
It is the most popular type of SQL injection. This type of attack uses the UNION statement, which is the integration of two select statements, to obtain data from the database.

Error-Based SQL Injection
An error-based SQL injection is the simplest type; but, the only difficulty with this method is that it runs only with MS-SQL Server. In this attack, we cause an application to show an error to extract the database. Normally, you ask a question to the database, and it responds with an error including the data you asked for.

Blind SQL Injection
The blind SQL injection is the hardest type. In this attack, no error messages are received from the database; hence, we extract the data by asking questions to the database. The blind SQL injection is further divided into two kinds:
1. Boolean-based SQL injection
2. Time-based SQL injection
The above techniques can be used to obtain the data in the database by either asking a question or inducing a time delay.


SQL Injection types
Error based Injection:

The attacker sends some malicious query to the database which results in errors.  The errors should be very generic, otherwise, they may give useful hints to the attacker.

Comment-Line: Using comment line to cause the database to ignore a part of a valid query.

E.g. Select * from stores where product_id = blah’ or 1=1-- (everything after this will be neglected)

Tautology: There are a lot of strings which always evaluates to be true, like ‘1’ = ‘1’ ‘a’ = ‘a’, etc., using them in the query to create constantly true conditions.

E.g. Select * from users where username=’blah’ or ‘a’=’a’ -- and password=’pass’
Union Based SQL injection:

Using union command in SQL query to execute additional queries; thereby, modifying/inserting/deleting or dropping the contents of the table.

E.g. Select * from stores where product_id=1 union select 1,database(),user(),4#

Stored procedures: Creating malicious inputs to execute malicious queries.

Incorrect queries: Coming up with logically incorrect queries to see the error messages to get more information about the target database.

Select * from stores where id=1’

The above query will result in a syntax error and might reveal the backend database type.
Blind SQL injection:

This is a type of SQL injection where we don’t have a clue as to whether the web application is vulnerable to injection attack or not.
Types:

Boolean: Only correct queries show the result, wrong queries do not return anything. Attackers should try to generate logically correct queries.

boolean type

If suppose the original query to the database is

Select * from users where id=’id.txt’

If we give blah’ and 1=1# as input which evaluates to be a right query

Select * from users where id=’blah’ or 1=1#, we will see the user results.

If we give blah’ and 1=2# as input which is a wrong query then we don’t see any results.

Select * from users where id=’blah’ or 1=2#

Time delay:  Depending on some conditions, setting a time delay. If that condition is satisfied, we can observe the time delay; thereby, concluding that the input we gave produced a positive result. This is a time consuming process.