---
title: "Temporary Tables Magic Table and Injection in SQL"  
description: "Tempopary tables are created at runtimes and ability to perform all the operation like  the normal table. These tables have limited scope. This table"  
author: "Allen Scott"  
published: 2016-10-06  
updated: 2018-03-16  
canonical: https://www.mindstick.com/blog/11216/temporary-tables-magic-table-and-injection-in-sql  
category: "database"  
tags: ["sql server", "sql"]  
reading_time: 3 minutes  

---

# Temporary Tables Magic Table and Injection in SQL

**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](https://www.mindstick.com/articles/12546/temporary-table-in-sql):**

**[Local Temporary](https://www.mindstick.com/forum/33543/how-to-create-local-temporary-table-in-sqlserver) 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](https://www.mindstick.com/forum/33544/how-to-create-global-temporary-table-in-sqlserver) 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.

**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](https://www.mindstick.com/forum/205/find-the-all-column-with-schema-for-any-table-in-sql-server) inserted and deleted.

[As the name](https://www.mindstick.com/forum/34352/the-term-update-database-is-not-recognized-as-the-name-of-a-cmdlet-function) 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 tableCreate TABLE StuHis(Roll INT,NAME VARCHAR (100),Timestampinsert datetime,message_audit varchar(100),)Create the trigger for your info tableCreate trigger infotrigger on  StdInfoFor insert ASDeclare @Roll int;Declare @NAME varchar(100);Declare @message_audit varchar(100);Select @Roll=I.Roll from inserted ISELECT @NAME= I.NAME FROM INSERTED ISET @message_audit='inserted successfully, your action isrecorded';insert into StuHis(Roll,[NAME],Timestampinsert,[message_audit])values (@Roll,@NAME,GETDATE(),@message_audit)PRINT 'For any error please connect with youradmistrative'GO
```

**Injection in SQL**

When malicious code inserted in place of some valid text required from the user side. Then it is [called as](https://www.mindstick.com/forum/471/why-is-white-box-testing-called-as-glass-box-testing) Injection in SQL. When we submit the code then it will process the malicious code.

**For example:**

```
Update stu_infoSet salary=@SalWhereName=@Name
```

if supose user inserted the code in place of name

@Name : A;[Drop table](https://answers.mindstick.com/qa/93949/how-to-drop-table-in-sql-server-using-command) 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](https://www.mindstick.com/blog/227/sql-injection)

**So we need to use SQL parameter to prevent injection:**

SQL parameter is the values that are added to an [SQL query](https://www.mindstick.com/forum/529/rename-table-name-and-column-name-using-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](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net). In method we need to use sp_executesql. It can accept parameterized variables only

```
CREATE PROCEDURE GetCustomerDetails      @ID CHAR(5)BEGIN      DECLARE @SQL NVARCHAR(2000)      SET @SQL =ENDAS'SELECT ContactName FROM Customers WHERE Id = @Id'      EXECsp_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.

---

Original Source: https://www.mindstick.com/blog/11216/temporary-tables-magic-table-and-injection-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.