Posts Tagged ‘SQL Server’

Magic Tables are invisible tables which created on MS SQL Server, during INSERT/UPDATE/DELETE operations on any table. These tables temporarily persists values before completing the DML statements.
Magic Tables are
internal table which is used by the SQL server to recover recently inserted, deleted and updated data into SQL server database. That is when we insert or delete any record from any table in SQL server then recently inserted or deleted data from table also inserted into inserted magic table or deleted magic table with help of which we can recover data which is recently used to modify data into table either use in delete, insert or update to table. Basically there are two types of magic table in SQL server namely: INSERTED and DELETED, update can be performed with help of these twos. When we update the record from the table, the INSERTED table contains new values and DELETED table contains the old values. Magic Tables does not contain the information about the Columns of the Data Type text , ntext or image. These are maintained by SQL Server for internal processing whenever an update,insert,Delete occur on table.
These
Magic tables is used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only while in SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also. Magic tables with Non-Trigger activities uses OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.

Suppose we have Employee table, Now We need to create two triggers to see data with in virtual tables Inserted and Deleted and without triggers.

For INSERTED virtual table, using Trigger ( Magic Tables with Triggers)
CREATE TRIGGER trg_Emp_Ins ON Employee
FOR INSERT
AS
begin
/* Here you can write your required codes, but I am here putting only demostration of Magic Table.*/
Print ‘Data in INSERTED Table’
SELECT FROM INSERTED — It will show data in Inserted virtual table
Print ‘Data in DELETED Table’
SELECT FROM DELETED — It will show data in Deleted virtual table
end
–Now insert a new record in Employee table to see data with in Inserted virtual tables
INSERT INTO Employee(NameBasicSalaryVALUES(‘Virendra’,2000)

For INSERTED virtual table, without Trigger ( Magic Tables with Non-Triggers)

— Use INSERTED Magic Tables with OUTPUT Clause to Insert values in Temp Table or Table Variable

create table #CopyEMP(CName varchar(12),CDept varchar(12))
–Now insert a new record in Employee table to see data with in Inserted virtual tables

Insert into Employee(NameBasicSalaryOUTPUT INSERTED.NameINSERTED.BasicSalary into #CopyEMP values(‘Ram’,‘100’)
Select from #CopyEMP

For DELETED virtual table, using Trigger ( Magic Tables with Triggers)

CREATE TRIGGER trg_Emp_Ins OEmployee
FOR DELETE
AS
Begin
/* Here you can write your required codes, but I am here putting only demostration of Magic Table.*/
Print ‘INSERTED Table’
SELECT FROM INSERTED — It will show data in Inserted virtual table
Print ‘DELETED Table’
SELECT FROM DELETED — It will show data in Deleted virtual table
End
–Now Delete few records in Employee table to see data with in DELETED virtual tables
DELETE Employee where BasicSalary > 10000

For DELETED virtual table, without Trigger ( Magic Tables with Non-Triggers)

— Use DELETED Magic Tables with OUTPUT Clause to Insert values in Temp Table or Table Variable

create table #CopyEMP(CName varchar(12),CDept varchar(12))

–Now Delete record in Employee table to see data with in DELETED virtual tables

DELETE Employee where BasicSalary > 10000

Select from #CopyEMP

Advertisements

SQL injection is a technique often used to attack a website.It is an attack in which attacker take the advantage of insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website. This attack can happen with the applications in which SQL queries are generated in the code. The attacker tries to inject their own SQL into the statement that the application will use to query the database.
Examples-
1) For example suppose the below query string is used for a search feature in a website and a user may have the inserted “VIRENDRA” as the keyword to search. If in the code behind the keyword is directly used into the SQL statement, it will be like.
String sql = “Select EmpName, City from EmployeeMaster where EmpName like ‘%” + txtSearch.Text + “%'”;
But the attacker might enter the keyword like
‘ UNION SELECT name, type, id from sysobjects;–
This way attacker can get details of all the objects of the application database and using that attacker can steal further information.

2) Web applications use string queries, where the string contains both the query itself and its parameters. The string is built using server-side script languages such as ASP, JSP and CGI, and is then sent to the database server as a single SQL statement. The following example demonstrates an ASP code that generates a SQL query.

sql_query= SELECT ProductName, ProductDescription FROM Products WHERE ProductNumber = ” & Request.QueryString(“ProductID”)
The call Request.QueryString(“ProductID”) extracts the value of the Web form variable ProductID so that it can be appended as the SELECT condition.
When a user enters the following URL:
http://www.mydomain.com/products/products.asp?productid=123

The corresponding SQL query is executed:

SELECT ProductName, ProductDescription FROM Products WHERE ProductNumber = 123

An attacker may abuse the fact that the ProductID parameter is passed to the database without sufficient validation. The attacker can manipulate the parameter’s value to build malicious SQL statements. For example, setting the value “123 OR 1=1” to the ProductID variable results in the following URL:

http://www.mydomain.com/products/products.asp?productid=123 or 1=1

The corresponding SQL Statement is:

SELECT ProductName, Product Description FROM Products WHERE ProductNumber = 123 OR 1=1

This condition would always be true and all ProductName and ProductDescription pairs are returned. The attacker can manipulate the application even further by inserting malicious commands. For example, an attacker can request the following URL:

http://www.mydomain.com/products/products.asp?productid=123; DROP TABLE Products

In this example the semicolon is used to pass the database server multiple statements in a single execution. The second statement is “DROP TABLE Products” which causes SQL Server to delete the entire Products table.

An attacker may use SQL injection to retrieve data from other tables as well. This can be done using the SQL UNION SELECT statement. The UNION SELECT statement allows the chaining of two separate SQL SELECT queries that have nothing in common. For example, consider the following SQL query:

SELECT ProductName, ProductDescription FROM Products WHERE ProductID = ‘123’ UNION SELECT Username, Password FROM Users;

The result of this query is a table with two columns, containing the results of the first and second queries, respectively. An attacker may use this type of SQL injection by requesting the following URL:

http://www.mydomain.com/products/products.asp?productid=123 UNION SELECT user-name, password FROM USERS

The security model used by many Web applications assumes that an SQL query is a trusted command. This enables attackers to exploit SQL queries to circumvent access controls, authentication and authorization checks. In some instances, SQL queries may allow access to host operating system level commands. This can be done using stored procedures. Stored procedures are SQL procedures usually bundled with the database server. For example, the extended stored procedure xp_cmdshell executes operating system commands in the context of a Microsoft SQL Server. Using the same example, the attacker can set the value of ProductID to be “123;EXEC master..xp_cmdshell dir–“, which returns the list of files in the current directory of the SQL Server process or as well as pass any DOS command using cmdshell.


Hi, As DBA, its many time we have to faced Questions from Auditors or Clients where your SQL Server follow SQL Server Hardening best practices?, Here are some few as,

  • During installation, Install only required components, When the SQL Server installation is complete, harden the SQL Server environment.
  • After the installation, use the SQL Server Configuration Manager tool in order to disable unnecessary features and services.
  • Install the most recent critical fixes and service packs for both Windows and SQL Server.
  • When you’re selecting authentication modes, Windows Authentication is a more secure choice
  • If there is still a need to use SQL Authentication – enforce strong password policy.
  • Do not use the SA account for day-to-day administration, logging on to the server remotely, or having applications use it to connect to SQL. It is best if the SA account is disabled and renamed.
  • Create a role-based security policy with the Security Configuration Wizard tool.
  • Create policies with Policy Based Management enable/ disable unnecessary features and services.
  • After the server has been hardened, periodically asses the server’s security using the MBSA (Microsoft Baseline Security Analyzer) and SQL Server BPA (Best Practices Analyzer).
  • For production SQL Servers running mission-critical databases, either hide the instance or disable the SQL Server Browser service.
  • Change the default ports associated with the SQL Server installation to put off hackers from port-scanning the server.
  • Enable a firewall to filter unnecessary and unknown traffic.
  • At the very least, set security auditing to failed login attempts; otherwise, both failed and successful logins should be captured and monitored.
  • If upgrading previous versions of SQL Server, remove the BUILTIN/Administrators group from the SQL Server Logins.
  • Use the IIS Lockdown and URL Scan tools to harden IIS.

Dear Friends, As you know, It’s the great option to delete millions or billions of records using batches transactions, in this the logs does not grow drastically and makes recovery faster in the event of failures.
Its my observation, some time if we use CHECKPOINT in each transaction, give very fruitful performance in production environment, because Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

Let see an example,
Please Note : Here one point have to keep in mind , if we are using any date comparisons to delete records, have to store that value in a variable, will be much faster than using in query’s WHERE directly. Suppose we have to delete 60 days old records, then setting the date initially would make a difference instead of calculating for each row.

DECLARE @DelDate DATETIME;
SET 
@DelDate DATEADD(d60,GETDATE())
WHILE 1 = 1
    BEGIN
        — Choose records count as per your records size.
        DELETE 
TOP (1000FROM MyTableName WHERE column_name<@DelDate
        IF @@ROWCOUNT< 1000 BREAK;
        CHECKPOINT;
    END

One more approach , it may be this operation can generate a lot of disk I/O, So its recommend that you put a “WAITFOR DELAY ‘time_to_pass'” statement in the loop to reduce the stress on your server. Waiting for even a quarter of a second between DELETE executions can reduce stress on your production environment, but its better in case of Production Server if records amount are large not so huge.

DECLARE @DelDate DATETIME;
SET 
@DelDate DATEADD(d,-60,GETDATE());

WHILE 1 = 1
    BEGIN
     — Choose records count as per your records size.
        DELETE 
TOP (1000FROM MyTableName WHERE column_name @DelDate
        IF @@ROWCOUNT < 1000 BREAK;
        CHECKPOINT;
        WAITFOR 
DELAY ’00:00:00.250′ — wait for quarter second
    END

Hope you like this way to get better performance .

Here, The best practices used for Stored Procedure .

Stored Procedure Tunning 

You can also visit here http://youtu.be/A-17sMgQqnM

USE VirendraTest
GO

CREATE TABLE #Virendratemp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))

SET NOCOUNT ON

INSERT #Virendratemp
EXEC sp_msforeachtable ‘sp_spaceused ”?”’

SELECT a.Table_Name,
a.Row_count,
COUNT(*) AS Col_count,
a.Data_size
FROM #Virendratemp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ‘ KB’, ”) AS integer) DESC

DROP TABLE #Virendratemp