SQL Injection Attack
Posted by Ravi Khanal on Nov 10, 2008
SQL Injection is an attack technique used to exploit web sites by altering backend SQL Statements through manipulating application input. It is a technique that takes advantage of non-validated user input data to inject SQL Statements dynamically into the existing ones for execution, and hence, poses a severe security threat to the supposedly secure systems. If attacker can get through some of the system stored procedures and some database server functions, then not only they can steal data from the databases, but also modify and delete it.
Some Examples of SQL Injection attack:
Example 1:
If there is a form which gets value from a Form.
If the form looks like:
Enter Product ID: 124 OR 2=2
Then the corresponding SQL Statement for that will be
select * from products where ProductId = 124 OR 2=2
This statement will always return a Value since 2=2 is always true.
Example 2:
If there is a login validation form and a user inputs like this then,
Login: ‘OR”=’
Password: ‘OR”=’
then the corresponding SQL statement will be
Select IsAuthorized FROM Users where Login = ' ' OR"=" AND Password = ' ' OR"="
Read the rest of this entry »
T-SQL vs PL-SQL
Posted by Ravi Khanal on Oct 15, 2008
Microsoft’s T-SQL is similar to Oracle’s PL-SQL to some extent, but they are very different in some important and basic ways. Some of the basic differences are:
- The difference in the datatype:
- In T-SQL Datetime is represented as 1/300th Second and in PL-SQL date is represented as 1 second and Timestamp as 1/100 millionth Second.
- The default date format of PL-SQL doesnot include time part.
- In T-SQL, there is no need of dual. The T-SQL Syntax will look like:
select getdate()
The PL-SQL will look like
slect sysdate from dual - INSTEAD OF trigger is used in SQL Server as an equivalent to Oracle’s BEFORE trigger.
- CASE expressions can be used in SQL Server for Oracle’s DECODE() function.
- In T-SQL there is an IDENTITY function which is more simpler than the SEQUENCE of PL-SQL. For implementing Sequence in PL-SQL, you have to create a new object called SEQUENCE and also we should have the trigger.
- You cant have multiple databases on an Oracle Instance but you can have multiple databases on a SQL Server Instance.
- In T-SQL there is SELECT INTO. In PL-SQL you have to use INSERT INTO.
T-SQL:select getdate() newcolumn into newtable;
PL-SQL:insert into newtable(newcolumn) values (sysdate); - In PL-SQL there is MINUS operator. In T-SQL you have to use NOT EXISTS clause in Select statement to generate the same result.
- T-SQL’s RAISEERROR does not break the flow. It simply returns an error string or message but the stored procedure still returns normally. PL-SQL’s raise_application_error throws an exception, exits the current stored procedure, rolls back till implicit savepoint at the beginning of the stored procedure.
- In T-SQL, stored procedure do not rollback automatically if something fails. You can use TRY CATCH but still the roll back is optional. In PL-SQL, stored procedures are atomic. Any error inside a stored procedure rolls back up to the point where the stored procedure was called.
| T-SQL | PL-SQL |
| Integer, SmallInt, TinyInt, Bit | NUMBER |
| Varchar | Varchar2 |
| Text | CLOB |
| IMAGE | BLOB |
| BINARY(n), VARBINARY | RAW(n) or BLOB |
| MONEY | NUMBER(19,4) |
| DATETIME, SMALL-DATETIME | DATE |
| NVARCHAR(n) | VARCHAR(n*2) |
| TIMESTAMP | NUMBER |
Transaction log is full
Posted by Ravi Khanal on Sep 21, 2008
Transaction log is also major cause for slowing the Performance of SQL Server. As a Database Administrator you should monitor your transaction log and check whether it is full or not. Transaction log is a WRITE AHEAD LOG. So whenever there is any data modification in the database, then “before image” and “after image” of any data would be written in the transaction log. If the transactions that are making the data modifications is running or its not being committed then these logs in the transaction log would be the active portion of transaction log. The committed transaction log would be inactive log in the transaction log. The lazy writer thread wakes up after certain period of time and it would write those logs to the physical disks. But sometime if the transaction are too long and its doing lots of updates then it would make the transaction log full and you will get “Out of disk” error. If you are maintaing full Recovery mode and never taking any log backup then its another major cause for transaction log to become full. Transaction log is the important component of SQL Server for recovering from any disaster scenarios. So as a Database Administrator you should always have a close look on the Transaction Log.
So you should use some DBCC command on the regular basis to find out about the Transaction Log.
- DBCC SQLPERF(logspace)
This will tell you how much transaction log is being used.
- DBCC LOGINFO
This will tell you about the virtual logs inside the transaction log.
- DBCC OPENTRAN
This will show you the open transactions or the transaction that are not yet committed. Read the rest of this entry »
Tempdb best practices
Posted by Ravi Khanal on Aug 26, 2008
Tempdb database provides temporary space for various operations and is recreated every time SQL Server is restarted. It is a temporary database to store temporary tables, table variables, cursors, work tables, row versioning. Proper optimization of the tempdb database increases the performance of your SQL Server.
Some of the best Practices for tempdb are: Read the rest of this entry »
Tips for Performance Monitoring
Posted by Ravi Khanal on Jul 20, 2008
Performance Tuning is the most important job you should perform as a Database Administrator. The most important thing you should consider for monitoring the performance of the server are:
Troubleshoot the Slow running queries:
- Try to see whether a batch of commands are running or a single query is running.
- Examine the Execution plan by using SQL Profiler and SQL Query Analyzer.
- Verify the existence of the correct indexes by performing Index analysis option in SQL Query analyzer or Index tuning wizard. Check for the Table Scan or Index Scan or Index Seek. If there is Table Scan then there is no index in your table. If there is Index Scan then your index is not being used. If there is Index seek then your Index is performing good, check for other issues.
- Run the following DBCC Commands:
DBCC SHOWCONTIG(’JOBS’)
DBCC INDEXDEFRAG(NORTHWIND, ORDERS, CUSTOMERS )
DBCC DBREINDEX(AUTHORS, ‘clustered index’, 70)
-
Remove any Hints and re-evaluate the performance.
-
If a particular query is Slow when it is using Parallel execution, you can try forcing a non-parallel execution plan.
How to delete duplicate rows?
Posted by Ravi Khanal on Jul 8, 2008
Duplicate rows in a T-SQL can be deleted by using either ROWCOUNT or TOP operator.
If we have a Table name Employee with columns EmpId, EmpLname, EmpFname. If we have some duplicate data for EmpId=1
Then the syntax for deleting duplicate data using ROWCOUNT would be
Set ROWCOUNT 1
DELETE FROM EMPLOYEE WHERE EmpId=1
Set ROWCOUNT 0
By setting the ROWCOUNT 1 we can delete only one duplicate row.
Similarly, we can do it using TOP as,
SELECT TOP(1) FROM EMPLOYEE WHERE EmpId=1
Database Migration best practices
Posted by Ravi Khanal on Jun 4, 2008
Database Migration is always a very challenging job since data is the major component of any System and it should always be secured. You are not migrating database when any new version of the database is in the market. You have to spend a lot time researching whether the database migration is really fruitful. After the data migration from one version to another, you should have the all the usefullness of the previos version with the usefullness of the new one.
Some of the best practice that I used to follow while migrating database from SQL Server 2000 to SQL Server 2005 are:
Read the rest of this entry »
