Temporary Tables Magic Table and Injection in SQL
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:
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.
CREATE TABLE #EeLocTemp
Temporary table: This table is available for all the use that
are connected 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.
CREATE TABLE ##EeGloTemp
These tables hold the recent data from the insert, delete
update statement. There are two tyes of magic table in sql inserted and
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.
Create your table:
Create TABLE StdInfo
Create audit table
Create TABLE StuHis
NAME 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
insert into StuHis
PRINT 'For any error please connect with your
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.
if supose user inserted the code in place of name
@Name : A;Drop table Stu_info,
; means that new query executed
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
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 =
this @0 ensure that the value which may be inserted is
2nd method is stored procedure. In method we need to use
sp_executesql. It can accept parameterized variables only
CREATE PROCEDURE GetCustomerDetails
DECLARE @SQL NVARCHAR(2000)
SET @SQL =
'SELECT ContactName FROM Customers WHERE Id = @Id'
sp_executesql @SQL, N'@CustomerId CHAR(5)', @CustomerId = @CustId
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.