SQL Basics - Grouping Data

We previously learned now to use aggregate functions to rerieve the average of a column or of entries matching a specific condition filtered with WHERE. Now we will learn how to get all the averages of the table grouped by a column entry. For example, you are a teacher with 20 kids in your class. Each student took six exams and you want ot get each child's average. You can use AVG() and WHERE student_name='whatever', but you would have to do it 20 times for all your students. Here's where grouping comes into play.
SELECT AVG(test_score) FROM table_tests
GROUP BY student_name
Another example. Using our old database we'll get the sum of each order in a separate entry.
SELECT order_num, SUM(quantity*item_price) FROM OrderItems
GROUP BY order_num

How about if we want to get a list of all students with an average below 65. We would think to use the WHERE clause, but it won't work. WHERE filters rows only, not groups. For groups, we use the HAVING clause.
SELECT AVG(test_score) AS average
FROM table_tests
GROUP BY student_name
HAVING average <>
One more thing to note. If you were to have all your classes in one table and you wanted the average of all failing students from just your Grade 6 class, here's how you'll do it:
SELECT AVG(test_score) AS average
FROM table_tests
WHERE column_grade = 6
GROUP BY student_name
HAVING average <>
ORDER BY student_name
Notice I used ORDER BY to sort the data, because although GROUP BY groups the data by name, it will not necessarily be in alphabetical order. Note that the clauses must actually be in the above order. SELECT...FROM...WHERE...GROUP BY...HAVING...ORDER BY

SQL Basics - Using Functions (cont.)

Today we're going to talk about some more text manipulation functions that are a bit tricky to get right. The reason being that each application has different syntax for these functions. I will post all the different syntaxes for the popular programs and will indicate in a chart which syntax each program uses.

CONCATENATE

We already spoke about Concatenating, but I bring it up because the chart will tell us which syntax your program uses.

1. SELECT col1 col2 FROM tablename WHERE .....

2. SELECT col1 + col2 FROM tablename WHERE .....

3. SELECT CONCAT(col1,col2) FROM tablename WHERE .....

_________________________________________________

SUBSTRING

This function is similar to the LEFT and RIGHT function we learned about in last lesson. Except it takes a string from in middle of another string. For example, to take the third and fourth letter of the string in the column, we would enter it so:

1. SELECT SUBSTRING(colname FROM 3 FOR 2) FROM tablename WHERE .....

2. SELECT SUBSTR(colname,3,2) FROM tablename WHERE .....

3. SELECT MID(colname,3,2) FROM tablename WHERE .....

4. SELECT SUBSTRING(colname,3,2)FROM tablename WHERE .....

_________________________________________________

UPPER CASE

To retrieve the results all in upper case format, we use this command:

1. SELECT UPPER(colname) FROM tablename WHERE .....

2. SELECT UCase(colname) FROM tablename WHERE .....

_________________________________________________

LOWER CASE

To retrieve the results all in lower case format, we enter it like this:

1. SELECT LOWER(colname) FROM tablename WHERE .....

2. SELECT LCase(colname) FROM tablename WHERE .....

_________________________________________________

POSITION OF SUBSTRING IN STRING

To retrieve the position of a substring in a string, we use the SUBSTRING function. This is especially useful when finding out where a space is located to retrieve just the first word. To do that, we enter:

1. SELECT POSITION(' ' IN colname) FROM tablename WHERE.....

2. SELECT INSTR(colname, ' ') FROM tablename WHERE.....

3. SELECT CHARINDEX(' ',colname) FROM tablename WHERE.....

_________________________________________________

There are many more such functions, but these are the most important ones, especially if we're dealing with SQL injection. The following is a chart of the major Database applications and which syntax they use, using the numbered syntaxes above.

	Concatenate	Substring	Upper Case	Lower Case	Position
SQLite	1	1	1	1	1
DB2	1	2	1	1	1
Access	2	3	2	2	2
PostGres	1	1	1	1	1
Oracle	1	2	1	1	2
SQL Server	2	4	1	1	3
MySQL	3	1	1	1	1
Mimer	1	1	1	1	1
Sybase	1	4	1	1	3

We'll speak....

SQL Basics - Using Aggregate Functions

AGGREGATE FUNCTIONS
Aggregate functions are used to summarize values, such as getting the sum of many rows, the average, etc. Let's go through the most used functions.

AVERAGE
AVG() is used to get the average of a collection of rows.

Usage: SELECT AVG(colname) FROM ..... [WHERE...]
_________________________________________
COUNT
COUNT () is used to return the number of rows in a given column or rows that are defined in a WHERE clause.

Usage: SELECT COUNT(colname) FROM ..... [WHERE...]

Using the above syntax will return the number of rows that don't have a NULL value. To return the full number of rows including rows with a NULL value use:

SELECT COUNT(*) FROM .....
_________________________________________
MAX
MAX() is used to get the greatest value in the column or defined rows.

Usage: SELECT MAX(colname) FROM ..... [WHERE...]
__________________________________
MIN
MIN() returns the lowest value in the column or defined rows.

Usage: SELECT MAX(colname) FROM ..... [WHERE...]
_________________________________________
SUM
SUM() Returns the sum of the column or the defined rows.

Usage: SELECT SUM(colname) FROM ..... [WHERE...]

SQL Basics - Using Functions (Text Manipulation)

We really started working with functions in the last post using concatenation. Today we'll discuss one of two types of functions. The first is string manipulation functions, which concatenation is part of. The second category is the aggregate functions, which summarizes values, such as sums and averages. This second function will be discussed in the next post.

STRING MANIPULATION FUNCTIONS

LTRIM

This function is used to remove all spaces to the left of the table entry. For example, if the entry was ' hello', we only want the word 'hello to be returned, and to do this we use LTRIM.

Usage: SELECT LTRIM(colname) From .....
________________________________________

RTRIM

This function is used to remove all spaces to the right of the table entry. For example, if the entry was 'hello ', we only want the word 'hello to be returned, and to do this we use RTRIM

Usage: SELECT RTRIM(colname) From .....
___________________________________

LEFT

This function is used to return a specified number of characters starting at the left of the table entry. This is useful if a column of Zip Codes are saved in 00000-0000 (for you USA'ers) format and we only want the first five digits. For this we use LEFT

Usage: SELECT LEFT(colname,5) From .....
___________________________________

RIGHT

This function is used to return a specified number of characters starting at the right of the table entry. If we wanted to get the last 4 digits of a Social Security number (also for you USA'ers), you would use the RIGHT function.
Usage: SELECT RIGHT(colname,4) From .....
___________________________________

LEN

This function simply gets the length of the specified entry. For example, to get the lenght of a user's password, we use LEN.
Usage: SELECT LEN(pass_word) From .....

SQL Basics - Sorting Your Results

To sort your results returned from a SELECT command,we use the ORDER BY clause.

SYNTAX

SELECT * FROM table_name [WHERE condition]

ORDER BY column-name(s)

EXAMPLES

SELECT * FROM TeethEyes

ORDER BY child_age

You can also sort text in alphabetical order.

SELECT * FROM TeethEyes

ORDER BY child_Eyes

How about if you want to sort in a descending order, such as the older child first. You use the DESC parameter.

SELECT * FROM TeethEyes

ORDER BY child_age DESC

One last thing. You can sort by multiple columns, and have it sort first by one and then the other, for example:

SELECT * FROM TeethEyes WHERE child_teeth>3

ORDER BY child_eyes, child_age DESC

First it sorted by the eye color in ascending order, then it sort by age in descending order.

We'll be in touch.

SQL Basics - Using Wildcards With The LIKE Operator

If you've worked with computers you know about wildcards. In Windows '*' stands for an indefinite number of characters and '?' means one character. You can do a similar representation in SQL WHERE statements. In most SQL applications the '*' is replaced with '%' and the '?' is replaced by the '_' (underscore) symbol. In Microsoft Access you must use the standard wildcards, * and ?. The way we use it is with the LIKE operator:

SELECT * FROM column_name WHERE LIKE '__sample*'

What the above example does is find entries in column_name that have any two characters, then the word 'sample', then whatever else.

Here are some examples of the syntax:

SELECT child_name FROM TeethEyes

WHERE child_name LIKE '__rr%';

In Access you would type: WHERE LIKE '??rr*'

Here's another example:

SELECT child_name FROM TeethEyes

WHERE child_name LIKE '%y';

IMPORTANT NOTE: I think I failed to mention all along the difference when entering text strings or entering numbers. When entering text in the WHERE clause, the text is surrounded by 's. such as 'this'. Numbers are entered without the apostrophes. I'll edit the first WHERE post to note this there too.

SQL Basics - Calculated Fields and Aliases

Very often you will find that you need to display data not in it's original form. Such as totals, averages, or multiples, or just reformatted. We can usually do it with the application we're using, but it is more efficient to do it using SQL. What happens is that SQL creates a virtual column containing your calculated field.

Here are some of the most used operators.

CONCATENATION

This operator takes two or more strings  and connects them into one long string. For this we use the '+' symbol in MS Access and some others. In some programs '||' is used (two straight up lines.)

EXAMPLE

SELECT child_name + child_eyes FROM TeethEyes;

Not extremely readable, is it? Here's what we can do to help that

SELECT child_name + ' has ' + child_eyes + ' eyes.' FROM TeethEyes;

That's a little better.What we did is add the name, ' has ', which is a space, the word has ans a space. Then we added the eye color, a space and eyes. Pretty simple.

MATHEMATICAL FORMATTING

You can also use the '+' symbol on numbers to add them together. For that matter, you can use all 4 regular operators (+,-,*,/).

EXAMPLE

SELECT (quantity*item_price) FROM OrderItems WHERE order_num=20005

That's the number of items ordered multiplied by the price of the items, for order number 20005. If we want to get the total price for the order we would have to use SUM(), which we'll hopefully learn more about in another lesson.

SELECT SUM(quantity*item_price) FROM OrderItems WHERE order_num=20005

If you noticed, the column name in the past few pictures was Expr1000. That's the name automatically given to the virtual column by SQL. Sometimes we need to name the column so that our application can work with it. For this we use aliases, by adding the AS parameter.

SELECT SUM(quantity*item_price) AS order_total FROM OrderItems WHERE order_num=20005

As you see, now our virtual column has a name order_total, and as far as our application knows, it's a regular real column to work with.

SQL Basics - The WHERE Clause (AND OR and IN)

CHECKING FOR MULTIPLE CONDITIONS

In last lesson we learned about the WHERE clause used to filter the results. How about if you wanted to filter the results further so that the output matches in two aspects. For this we use the AND operator. For example:

SELECT prod_name FROM Products
WHERE vend_id='BRS01' AND prod_price<9;

This returns:

It returned only the name of the products that are from vendor BRS01 and that are priced under $9.00.

___________________________________________________

CHECKING FOR ONE OF MULTIPLE CONDITIONS

Now if we wanted to retrieve items that match one of various conditions, we would use the OR operator, like this:

SELECT * FROM OrderItems
WHERE quantity>50 OR item_price>10;

Here's what you get:

You can also use both AND and OR together. The AND will get processed first, and the OR second, so to use the OR first use parentheses, as shown below.

SELECT * FROM TeethEyes WHERE child_age<6 AND child_eyes='Gray' OR child_eyes='Brown'

SELECT * FROM TeethEyes WHERE child_age<6 AND (child_eyes='Gray' OR child_eyes='Brown')

In the first case we didn't use parentheses. It returned all entries that match either a child under 6 with brown eyes, or a child with gray eyes. In the second case, it returned children under 6 that have either Gray or Brown eyes.

___________________________________________________

A similar operator is the IN operator. It works almost the same as OR, with a different syntax.

SELECT * FROM TeethEyes
WHERE child_age IN(8,1,10)

___________________________________________________

Before we close out for the day, I'll mention two more operators, NOT and IS NULL. NOT works almost the same as'<>', only it comes before the column name.

SELECT vend_name,vend_country FROM Vendors
WHERE NOT vend_country='USA'

IS NULL checks for a NULL value in the given column. In our Vendors table, foreign companies don't have states listed so this is the result.

SELECT vend_name,vend_country FROM Vendors
WHERE vend_state IS NULL

SQL Basics - The WHERE Clause

The WHERE clause is used to filter through the table and extract only the rows you want.

BASIC SYNTAX

SELECT column FROM table WHERE column operator value

EXPLANATION

You already know what SELECT and FROM is, the new part is:

WHERE column operator value

What WHERE does is tell the SELECT Statement to only select the entries from the table that match a certain criteria. Let's give an example and then we'll explain it a bit more. Using our database we'll issue the following command

SELECT prod_id, prod_name FROM Products
WHERE vend_id='BRS01';

The output is shown below:

What'd we do? We selected the product ID and product name of the products whose vendor code is BRS01.

So WHERE column operator value means we write WHERE, the name of the column to check or 'vend_id', the operator which in our case was '=', and the value which was BRS01.

This is a list of the basic operators, although we will learn other ones in the future.

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range

Here's some examples of these operators using the table 'TeethEyes', shown below:

SELECT child_name, child_age FROM TeethEyes
WHERE child_teeth>18;

returns:

SELECT child_name FROM TeethEyes
WHERE child_age<=8;

returns:

Harry

Barry

Kari

And finally,

SELECT child_name,child_teeth,child_eyes FROM TeethEyes WHERE child_age BETWEEN 4 AND 9;

returns:

The WHERE statement is almost always used, and will be used extensively in SQL injection. Make sure you've got it so far.

Opening Database and Sending Queries

Here are the instructions on how to set up you're own practice range for SQL learning. I am going to write the instructions using Microsoft Access, because that is what I am using. You may want to use something else, especially if you're using Linux. There is a free Java based SQL client called Aqua Data Studio. It can be downloaded at http://www.aquafold.com/. If you go with that option, read up on their website about how to get started.

If you are using Microsoft Access, then read on:

You should already have the database downloaded from 2 posts ago, or find any other MS Access database online to use as a practice DB.

1. Click on the .mdb file you downloaded to open MS Access and the database. It will open with a smaller window inside that look like this:

Select 'Queries' from the side menu. Then click on 'New' from the top menu. In the 'New Query' popup, select 'New Design' and press OK. In the next popup, 'Show Table', press 'Close'.

Go to the 'View' menu on top, and select 'SQL View'.

In the window that opens is where you'll be doing you're magic. Access already puts in the SELECT statement because that is usually what you'll be using. When you finish typing your SQL query, you'll press the red exclamation mark on top to run it.

The output will be displayed in a new window. To go back to your SQL query, you'll choose SQL View again from the 'View' menu.

Now that you have the battleground ready, let's get going.

Learning the Basics of SQL - Part One - The SELECT Statement

Let's get straight down to business.

We'll start with the most often used statement:

THE SELECT STATEMENT:

Basic syntax:

SELECT column_name(s) FROM table_name

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

For example (using the table Products in the previous post):

SELECT prod_name FROM Products

Will return :

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

Or we can retrieve multiple columns such as:

SELECT prod_name, prod_price FROM Products

and you'll get:

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

Or you can use wildcards and retrieve all the columns:

SELECT * FROM Products

and you'll see:

In SQL, spaces don't count, and Enter presses don't either. Meaning that you can type:

SELECT * FROM Products

and get the same results.

Next we will discuss the WHERE clause. Before that, I will post instructions on how to open the database in Microsoft Access and send it SQL queries.

The Database On Which We'll Be Basing Our SQL Guide

I quickly learned SQL from a book named Sams Teach Yourself SQL in 10 Minutes (3rd Edition) (Sams Teach Yourself). It's well written and simply put. If you want to get more information than I'll be putting up, and don't want to spend to much time, I would recommend this book. They based it on a database available on their website. I don't know if they only let you download if you own the book, but it's available here:

www.forta.com/books/0672325675

I will also post the tables below, so that if you're not going through the guide hands-on, at least you'll be able to follow the outcomes.

Table named 'Customers':

cust_id	cust_name	cust_address	cust_city	cust_state	cust_zip	cust_country	cust_contact	cust_email
1000000001	Village Toys	200 Maple Lane	Detroit	MI	44444	USA	John Smith	sales@villagetoys.com
1000000002	Kids Place	333 South Lake Drive	Columbus	OH	43333	USA	Michelle Green
1000000003	Fun4All	1 Sunny Place	Muncie	IN	42222	USA	Jim Jones	jjones@fun4all.com
1000000004	Fun4All	829 Riverside Drive	Phoenix	AZ	88888	USA	Denise L. Stephens	dstephens@fun4all.com
1000000005	The Toy Store	4545 53rd Street	Chicago	IL	54545	USA	Kim Howard

Table named 'Orders':

order_num	order_date	cust_id
20005	5/1/2004	1000000001
20006	1/12/2004	1000000003
20007	1/30/2004	1000000004
20008	2/3/2004	1000000005
20009	2/8/2004	1000000001

Table named 'OrderItems':

order_num	order_item	prod_id	quantity	item_price
20005	1	BR01	100	$5.49
20005	2	BR03	100	$10.99
20006	1	BR01	20	$5.99
20006	2	BR02	10	$8.99
20006	3	BR03	10	$11.99
20007	1	BR03	50	$11.49
20007	2	BNBG01	100	$2.99
20007	3	BNBG02	100	$2.99
20007	4	BNBG03	100	$2.99
20007	5	RGAN01	50	$.4.49
20008	1	RGAN01	5	$4.99
20008	2	BR03	5	$11.99
20008	3	BNBG01	10	$3.49
20008	4	BNBG02	10	$3.49
20008	5	BNBG03	10	$3.49
20009	1	BNBG01	250	$2.49
20009	2	BNBG02	250	$2.49
20009	3	BNBG03	250	$2.49

Table named 'Products'

prod_id	vend_id	prod_name	prod_price	prod_desc
BNBG01	DLL01	Fish bean bag toy	$3.49	Fish bean bag toy, complete with bean bag worms with which to feed it
BNBG02	DLL01	Bird bean bag toy	$3.49	Bird bean bag toy, eggs are not included
BNBG03	DLL01	Rabbit bean bag toy	$3.49	Rabbit bean bag toy, comes with bean bag carrots
BR01	BRS01	8 inch teddy bear	$5.99	8 inch teddy bear, comes with cap and jacket
BR02	BRS01	12 inch teddy bear	$8.99	12 inch teddy bear, comes with cap and jacket
BR03	BRS01	18 inch teddy bear	$11.99	18 inch teddy bear, comes with cap and jacket
RGAN01	DLL01	Raggedy Ann	$4.99	18 inch Raggedy Ann doll
RYL01	FNG01	King doll	$9.49	12 inch king doll with royal garments and crown
RYL02	FNG01	Queen doll	$9.49	12 inch queen doll with royal garments and crown

Table named 'Vendors'

vend_id	vend_name	vend_address	vend_city	vend_state	vend_zip	vend_country
BRE02	Bear Emporium	500 Park Street	Anytown	OH	44333	USA
BRS01	Bears R Us	123 Main Street	Bear Town	MI	44444	USA
DLL01	Doll House Inc.	555 High Street	Dollsville	CA	99999	USA
FNG01	Fun and Games	42 Galaxy Road	London		N16 6PS	England
FRB01	Furball Inc.	1000 5th Avenue	New York	NY	11111	USA
JTS01	Jouets et ours	1 Rue Amusement	Paris		45678	France

These are the tables we'll be working with as we progress through the lessons. Looking forward.

An Introduction to SQL itself

Before we really start with SQL Injection, I think it is imperative that we first understand the SQL syntax on a basic level. I just finished reading a book Sams Teach Yourself SQL in 10 Minutes (3rd Edition) (Sams Teach Yourself).I must admit it took me quite a bit longer than 10 minutes, but it was definitely manageable. I skimmed through a big chunk and just got the needed information, which I will share with you. It will take me a few days to post the introduction, and only then will we continue with injections. (P.S. I only read the introduction afterwards. 10 minutes means it's split into 10 minute lessons.)

As I posted before, my computer was shipped out to be repaired. The screen has to be replaced. I was playing with an old laptop I have and got it working again. Anyway, I discovered a program called "Windows Live Writer", a free program from Microsoft. It let's you write up your posts offline and just upload it when ready. Hopefully it will help me make my posts a little neater, but don't bet on it.

We'll be in touch!!!