SQL Injection - Which may Destroy Your Database

SQL Injection :-

It is a Code Injection technique which might destroy your database knowingly or Unknowingly. We should all very careful about it. 

Here in this blog, we will see how Injection a) might destroy your database and how it b) will bypass security as well. Yes, lets explore with examples. 




Scenario 1  ( How to break Security Login )

Here, it is to check the User login with Username and Password. Getting the inputs and passed in a query. If it matches with the User List in the User Table, then it will allow that user to login. 

Input Values  - Case 1

User id   =  1001
Password = pass

The above given inputs will frame the SQL as below. 

Select * from User_table  where User id = 101  and Password = 'pass';

The above query will check Username and Password in the User table and allow if the Username and Password is perfectly matched with any of the records.

Here, there is no SQL Injection. Great. 


Input Values - Case 2

User id = 1001 OR 1=1 
Password = pass

The above said Input values  will make SQL statement as below. 

Select * from User_table  where User id = 101  or 1=1  and Password = 'pass';

This will select records always even though if any one enters wrong username and password. Then the application or database is in User's hand to do whatever he want. 

This is one way of doing SQL Injection. Be careful about it. 




Scenario 2  ( How to Inject to destroy Database by dropping key tables )

Here, it is to pass input value to frame  a SQL statement to Execute. 

Delete from transaction where transaction_no = "Input Value"

Input Value - Case 1:

Transaction Number =  100001

With the above said Input Value, the query will be framed to execute as below. 

Delete from transaction where transaction_no = 100001

Here, it is perfect and there is no SQL injection takes place. Perfect One. But.,  lets see in other two cases below. 


Input Value - Case 2:

Transaction Number =  100001  or  1=1 ;

With the above said Input Value, the query will be framed to execute as below. 

Delete from transaction where transaction_no = 100001 or  1=1;

We all know that, the above statement will not delete only the transaction 100001 but all the transactions in the transaction table, which is the crisis to the business. 



Input Value - Case 3:

Transaction Number =  100001; drop table USER_LOGIN ;

With the above said Input Value, the query will be framed to execute as below. 

Delete from transaction where transaction_no = 100001; drop table USER_LOGIN;

Think about it, after deleting transaction what will happen. The key table USER_LOGIN will be dropped which may lead to DB Login Crash. 


Solution :-
1. Be very very careful about Input Parameters
2. Include this SQL injection scenarios in all your test cases during Testing.  

Avoid SQL Injection and Keep our database safe always.


Thanks for all your support. We will connect in our next blog with different topic

Regards,
Sathish

Table Partitioning in Oracle

Partitioning is nothing but the concept of "Divide & Rule"   or   "Categorize them to handle easily".

In order to handle data in huge tables in a easy and quick way,
Oracle has an effective concept " Partitioning Tables"  and  " Partitioned Indexes"

Lets start knowing about what it is all about, how it works and benefits out of it.


Say.. "Hai" to Partitioning  


Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition.

Each Partition has its own name. To retrieve the data, it can be retrieved by calling a table or by calling its partition unit. 


Pictorial view of Partitioned Table and Non Partitioned Table




When actually we can partition a table ?

Huge tables which has data more than 2 GB size can go for partitioning.

Table which has years of data, but mostly we deal with recent months data and old data in a table are just to read.

Table which has data can be categorized into types like Department wise, Country wise, etc.,  So that we can deal with those particular category data alone by naming them as partition.


Types of Partitioning :-

The three types of  partitioning are List, Range and Hash.





Below are the lines to support in explaining the above pictorial view of different partitioning types. 


List Partitioning :-

List partitioning segregates data by its data  which falls into different categories. 

For example:-
The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

A query to retrieve data only from Florida can deal with "East Sales Region" Partition alone.

Example code to create list partition of table 

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

Range Partitioning :-

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.  

Example Dates between 2000 to 2012, 2012 to 2014 , 2012 to 2016 and greater than 2016.  

Example: Revenue less than 1 million, less than 2 million, less than 4 million and Default ( other values will fall in default partition always )


Example Code :-

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
    TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 );

Hash Partitioning:-

No criteria involved. Just spread the data among partitions in equal size. 

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. 

The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Example code :- 

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4 
   STORE IN (gear1, gear2, gear3, gear4);

Composite Partitioning:-

We can have composite partitioning with the combination of  different partitions at different levels. 

Region wise(List) .. within region, year wise. (Range). within year  equal distribution ( hash ) partition..







Benefits of Partitioning :- 

There are three key benefits and they are 

Performance :- By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. It is like taking data from 1 million records rather than 1 billion records is always quicker

Manageability:- With partitioning, maintenance operations can be focused on particular portions of tables. For example, a database administrator could back up a single partition of a table, rather than backing up the entire table

Availability :- if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table.



Hope this helps.. if yes, then please share this post and comment your feedback.