SQL Injection & Prevention

In this tutorial we will take a look atSQL Injection, how to attack using sql injection and how we can prevent ourselves form it. Firstly we see what is it after all, SQL Injection is subset of the an unverified/unsanitized user input vulnerability ("buffer overflows" are a different subset), and the idea is to convince the application to run SQL code that was not intended. If the application is creating SQL strings naively on the fly and then running them, it's straightforward to create some real surprises. In this tutorial we will discuss only one and most common type to attack that is done usually on the login.

Typically the code we used for loging a user is some what similar to the following.

// ...
    $username=$_REQUEST["username"];
    $password=$_REQUEST["password"];

    $query="select * from user where username='$username' AND password='$password'";

    $result=mysql_query($query);
    $count=$mysql_num_rows($result);

    if($count==1){
        // after login process goes here
    }
    if($coun!=1){
        // Declined user process goes here
    }
    // ...

this work perfectly fine until an invader comes and tried to run credentials like

User Name : admin
Password  : FAKE_PASSWORD' OR 'x'='x

if the above credentials are passed the query rendered will be

select * from user where 
username='admin' 
AND password='FAKE_PASSWORD' 
OR 'x'='x' 

which is a legal SQL query it also satisfy our login criteria and hence an ilegimate user will be granted access.

Prevention

if we want to defend ourselves from this type of attack we have many solution available like

$query = sprintf("SELECT * FROM `user` WHERE username='%s' AND password='%s'",
                  mysql_real_escape_string($username),
                  mysql_real_escape_string($password));
mysql_query($query);

or you can use Object Relation Mapping for may be Prepared statement... but i use the following code to prevent myself from it.

// ...
    $username=$_REQUEST["username"];
    $password=md5(md5($_REQUEST["password"]));
    // Double MD5 are hard to find

    $query="select * from user where username='$username'";

    $result=mysql_query($query);
    $obj=mysql_fetch_object($result);

    $dbPass=$obj->Password;
    // password stored with Double MD5

    if($dbPass==$password){
        // after login process goes here
    }
    else{
        // Declined user process goes here
    }
    // ...

in my case i don't send password to the database just fetch the password and compare it.

i find it easy to implement... do you?

Continue Reading...

ROWNUM in Oracle

ROWNUM is a pseudo column(pseduo = not real) in oracle. It count the number of records returned in result set. The first record that comes in the result set increments the ROWNUM counter to 1, second record make ROWNUM = 2 , so on so forth.

ROWNUM is a magic column in Oracle Database that gets many people into trouble. When you learn what it is and how it works, however, it can be very useful. I use it for two main things:

1. To perform top- N processing. This is similar to using the LIMIT clause, available in some other databases.
2. To paginate through a query, typically in a stateless environment such as the Web.

How ROWNUM Works

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure:

select ..., ROWNUM
  from t
 where where clause="">
 group by <columns>
having <having clause="">
 order by <columns>;

Think of it as being processed in this order:

1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.

That is why a query in the following form is almost certainly an error:

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

The intention was most likely to get the five highest-paid people—a top- N query. What the query will return is five random records (the first five the query happens to hit), sorted by salary. The procedural pseudocode for this query is as follows:

ROWNUM = 1
for x in 
( select * from emp )
loop
    exit when NOT(ROWNUM <= 5)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

It gets the first five records and then sorts them. A query with WHERE ROWNUM = 5 or WHERE ROWNUM > 5 doesn't make sense. This is because a ROWNUM value is assigned to a row during the predicate evaluation and gets incremented only after a row passes the WHERE clause. Here is the correct version of this query:

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

This version will sort EMP by salary descending and then return the first five records it encounters (the top-five records). As you'll see in the top- N discussion coming up shortly, Oracle Database doesn't really sort the entire result set—it is smarter than that—but conceptually that is what takes place.

Continue Reading...

Difference Between Dates in MySQL

Find difference between two dates is very easy to find out using simple SQL queries, suppose we want to check,the duration a user logged in.

SELECT 
TIME_TO_SEC( TIMEDIFF( COL_LOGOUT_TIME , COL_LOGIN_TIME ) ) 
AS INTERVAL_IN_SECONDS
FROM Users

Above query will return number of seconds between last logout and login time you can format it using PHP strtotime() and date() functions , COL_LOGOUT_TIME,COL_LOGIN_TIME are column names.

There is another scenario where one may wish to see the number of days since user last logged in.

SELECT 
DATEDIFF(CURDATE(), COL_LOGOUT_TIME) 
AS INTERVAL_IN_DAYS 
FROM Users 

above query will return number of days between current date and COL_LOGOUT_TIME.

Continue Reading...