Temporary Tables Magic Table and Injection in SQL

Submitted by Aliya Crox on Friday, October 7, 2016 - 08:15.

Tempopary tables Tempopary tables are created at runtimes and ability to perform all the operation like the normal table. These tables have limited scope. This table is stored in tempdb. There are two types of temporary table: Local Temporary table: This table is available only for the current connection. It is automatic deleted once disconnects from instance. This table is createdby putting single #(Hash) before the name of the table. Example:


CREATE TABLE #EeLocTemp
 (  
Internal_id Int,  
Name varchar(150)
)

Global Temporary table: This table is available for all the use that are concected with the server. It is automatic deleted once disconnects from all the userside. This table is createdby putting double ##(Hash) before the name of the table. Example:

CREATE TABLE ##EeGloTemp
 (  
Internal_id Int,  
Name varchar(150)
)
Magic table

These tables hold the recent data from the insert, delete update statement. There are two tyes of magic table in sql inserted and deleted. As the name suggest insert record is stored in inserted magic table and delete record is stored in deleted magic table. Update record is stored in Inserted table. There is no separate table for updated data. In previous version of SQL this table is only used in trigger but now they can be used in non-trigger statement. For Insert Create your table:

Create TABLE StdInfo
(
Roll INT,
NAME VARCHAR(100),
)
Create audit table
Create TABLE StuHis
(
Roll INT,
NAME VARCHAR (100),
Timestampinsert datetime,
message_audit varchar(100),
)
Create the trigger for your info table
Create trigger infotrigger on  StdInfo
For insert
AS
Declare @Roll int;
Declare @NAME varchar(100);
Declare @message_audit varchar(100);
Select @Roll=I.Roll from inserted I
SELECT @NAME= I.NAME FROM INSERTED I
SET @message_audit='inserted successfully, your action is recorded';
insert into StuHis (Roll,[NAME],Timestampinsert,[message_audit])
values (@Roll,@NAME,GETDATE(),@message_audit)
PRINT 'For any error please connect with your admistrative'
GO

Injection in SQL When malicious code inserted in place of some valid text required from the user side. Then it is called as Injection in SQL. When we submit the code then it will process the malicious code. For example:

Update stu_info
Set salary=@Sal
Where
Name=@Name

if supose user inserted the code in place of name

@Name : A;Drop table Stu_info,

; means that new query executed

Protection Mthod: Web developer uses the blacklist of words to avoid injection. This method not in use because drop and delete are some common word which is used in common English language. There are many ways by which we can prevent the sql injection So we need to use SQL parameter to prevent injection: SQL parameter is the values that are added to an SQL query at execution time in a controlled manner.

User_Id = getRequestString("UserId");
InjSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(InjSQL,txtUserId);
this @0 ensure that the value which may be inserted is userid only.
2nd method is stored procedure. In method we need to use sp_executesql. It can accept parameterized variables only

CREATE PROCEDURE GetCustomerDetails
      @ID CHAR(5)
AS
BEGIN
      DECLARE @SQL NVARCHAR(2000)
      SET @SQL = 'SELECT ContactName FROM Customers WHERE Id = @Id'
      EXEC sp_executesql @SQL, N'@CustomerId CHAR(5)', @CustomerId = @CustId
END

sp_executesql accept only parametric data so it any malicious code inserted by the user then it treated as text for search item not the part of the query. Validate all string entered by the user that it can not contain any character value Visit this related helpful article https://www.mindstick.com/blog/211/magic-table-in-sql-server What are temporary tables in Sql Server

Add new comment

25 views