Architecture Of A Query

So, A user Visits a webpage, and finds a Nice Golden Watch With some Offer, and thinks He should purchase it.

He will click on the option which shows him the Golden Watch.Now what exactly is happening here after he clicks?

Okay This is what we will be discussing today.

I assume you have knowledge of HTML, The GET and POST Parameters, once he clicks on the option, A GET or A POST Request is being made, this parameter can be seen on the HTML Source Code, and stays in the <form> tag,

Ex:

<form method="GET/POST" action="somepage.php">
// Codes Here
// Codes Here
</form>

As a quick review the Difference between a GET and a POST is when GET method is used, the address bar shows what all information are passed, whereas with the POST Method, it stays hidden.

Now, Once the request is made, lets say a PHP script handles the Form request, which then connects to Database to fetch the result, and spit back to the browser.

This Picture should give a basic idea about the architecture:

www.sqlihack.blogspot.com-3 tier architecture

Here is sample code what happens after the HTML form send the request, and then arrives to PHP scripts, for further processing:

<?php

// connects to the database
$conn = mysql_connect("localhost","username","password");

// dynamically build the sql statement with the input
$query = "SELECT * FROM Products WHERE id = '$_GET["val"]' ";

// execute the query against the database
$result = mysql_query($query);

// iterate through the record set
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
// display the results to the browser
echo "Description : {$row['ProductDescription']} <br>" .
"Product ID : {$row['ProductID']} <br>" .
"Price : {$row['Price']} <br><br>";
}
?>

So if we notice the PHP script, we see that, first It creates a connection to the database with username and password, then it builds the SQL Query that is to be passed to the database., next it fetches the results and then it echoes back to the users browser.

Now, if you have a question or if you had a thought waaoo, the username and password to connect to the database is right on the php script, so if we look at the php script we can get the username and password. Yes of course you are correct, only if you can see it, you cannot see a PHP script, PHP scripts are hidden by the browser.

So this was a basic 3 Tier Architecture that runs behind, there is also another Architecture which We will be discussing on the next post is the 4 Tier Architecture.

You must know these things before you start playing, because if you do not know these stuffs, you can never make or build your own Injecting queries.

Hope you are liking it. Thanks!

How A Login Form Works

Hello Friends, enough of me giving you lectures about SQL Queries, lets now speak something that might interest you.

As you might know, in IT world, every Output, is a result of some input and some processing.

Lets now look at a page where we have a place to type something(input), internally some processing happens based on which we would get a result.

I will speak about the processing later, but the first things first.

Lets have a close in the "form" section of a Web Page.

<form method="post" action="/validate.php">
Username: <input type="text" name="uname" />
Password: <input type="password" name="upassword" />
<input type="button" value="Login" name="login" />
</form>

[Note: I assume you know some HTML codings]

The above <form> section will have the below look

Username:

Password: 

So, basically when user types in the username and his password and clicks on the Login button, it executes a PHP script, to validate the input, based on which an output would be displayed.

So once the login button is pressed, PHP or ASP script would run behind to connect to a Database, lets assume here MySQL Database.


I will show the PHP script that connects to Database Later.

Lets try to understand with the help of SQL query that PHP script executes.

So lets say it connects to a Database, to a table named "users"

Table: users

name           password
whiskey        abc@123
Admin        d0nth@ckm3

The PHP script would execute the below SQL Query

SELECT * FROM users
WHERE
name = "Whatever_userInputs"
AND
password = "Whatever_userInputs"

So now if the user inputs "name" as "whiskey" and "password" as "123@abc", this would return False, to get the value as True, both the name and password has to match in the "users" Table.

And lets say its coded as if the Value of Query is True, it would login the user, otherwise it would not.

Interesting isn't it.... Well I hope this gives you a basic idea what happens behind the curtain.

This is just a basic foundation, and thats all for this post. Dont Forget to check my next post.

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 IN

Welcome Friends to this post where I will be discussing about the SQL IN Operator, basically this is very simple, and helps to extract records according to values of a Column.

Lets have a look at out 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 once again our Manager comes and asks for info about Whiskey, Rose and Mikey.

So in these case, what we can do is, use the SQL IN Query.

Syntax:

SELECT * FROM table_name
WHERE column_name IN (value1, value2, value3, . . .)

So for our purpose we would use the below query

SELECT * FROM Employee
WHERE Name IN ("Whiskey", "Rose", "Mikey")

So with this query, our result set will be:

PID    Name        Designation
222     Whiskey       Moderator
666     Rose             Admin
999     Mikey           Admin

This is cool isn't it? Anyways this is all what I have for you in this post.

Hope you enjoyed it and Don't forget to check my next post.

Thank You!

SQL LIKE and NOT LIKE

Hello Friends, Welcome to this post where I will be discussing about the SQL LIKE and NOT LIKE operator, the LIKE and NOT LIKE operator works wildcard characters.

Wildcard characters are a search criteria, where we use some symbol to match conditions, in SQL with LIKE we use the '%' symbol.

Lets look at an example, where our Table name is Employee.

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 check out the different LIKE query

1.

SELECT * FROM Employee
WHERE Name LIKE "Wh%"

This would display all the records for the Employees, whose name starts with "Wh"

2.

SELECT * FROM Employee
WHERE Name LIKE "%y"

This would display all the records for the Employees, whose name ends with "y"

3.

SELECT * FROM Employee
WHERE Name LIKE "%a%"

This would display all the records for the Employees, whose name has the letter "a" in it

4.

SELECT * FROM Employee
WHERE Name NOT LIKE "%a%

This would display all the records for the Employees, whose name does not contain the letter "a" in it.

So this is what I got for you in this post, hope you liked it, and Dont forget to check my next post.

Thank You!

SQL DELETE

Hello Friends, Welcome to this post, where I will discuss about another SQL query known as the DELETE query.

Now we have to be extra cautious with this DELETE query, as with the DELETE query we can delete records or a whole Table.

Syntax:

DELETE FROM table_name
WHERE some_column=some_value

So 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

Now manager walks into My cabin and says, you know I have thrown out Whiskey from our company, so delete all his records.

So our Query would be:

DELETE FROM Employee
WHERE Name="Whiskey"

To be more specific,

DELETE FROM Employee
WHERE Name="Whiskey" AND PID=222

So our result set will be:

PID    Name        Designation
444    Onty             Admin
111     Gaurav         Admin
333    Kumaar        Admin
555    Rishabh        Admin
666    Rose             Admin

Now, if you do this:

DELETE * FROM Employee

All the records of Employee Table will be deleted. And you would find the Manager walking into your cabin the next moment screaming at you and saying, "Even you are fired from job with Whiskey"

So you see how hazardous one statement can be, and you have to be very careful using this query.

Hope this was informative, and don't forget to check my next post.

Thank You!