SQL Injection

Question

Hi there,

I have SQL 2008 R2 server, it is hacked by SQL injection attack.  How can find the malicious code? how to protect the sql injection again?

Thanks,

---

stephen

Answer 1

Hi Stephenwhx,

I suggest you to read the following article which gives some useful tips to prevent SQL Server injection:

http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev

I suggest you to implement DDL Logging on your SQL Server using either Event Notification, Audit Specification and DDL Trigger. I also recommend you to implement Connection auditing in SQL Server so that all login attempts are logged inside ERROR Log

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Where can find the malicious code? I have tried to restore the DB, but it is hacked again. I need to find the malicious code. I have read the article, it does not help me now. 

Thanks,

---

stephen

Answer 3

Hi Stephenwhx,

Check for the following:

1) Restart the SQL Server in single user mode. Click the link following link on how to start SQL Server in single user mode:

http://msdn.microsoft.com/en-us/library/ms188236.aspx

2) Launch SSMS and execute the following query to find the startup procedures. You can use the following query for that:

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
DECLARE @procid INT
CREATE TABLE #tmp(objectname varchar(100),startup_status varchar(50)) 
DECLARE proc_cursor CURSOR FOR SELECT id FROM master.dbo.sysobjects WHERE TYPE IN ('P','X') 
OPEN proc_cursor  
FETCH NEXT FROM proc_cursor INTO @procid 
WHILE @@FETCH_STATUS = 0 
BEGIN
INSERT #tmp   
SELECT OBJECT_NAME(@procid),CASE Objectproperty(@procid,'ExecIsStartup') WHEN 1 then 'Enabled' else 'Disabled' end
FETCH NEXT FROM proc_cursor INTO @procid 
END
CLOSE proc_cursor 
DEALLOCATE proc_cursor 
SELECT * FROM #tmp WHERE startup_status = 'Enabled'
DROP TABLE #tmp 

3) Examine your SQL Server Error for anything that looks daughy.

4) Enable Logon Auding and Common Compliance Critera. You can do this by right-clicking SQL Server and Choose Properties and Then Activate Security tab. 

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 4

Thanks, I will try this solution. I will let you know whether it works.

---

stephen

Answer 5

this is also an added advantage

http://www.greensql.net/

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 6

from the event logs, I find out many the similar event:

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: Logon 
Event ID: 18456
Date: 8/16/2012
Time: 2:35:05 PM
User: N/A
Computer: BUSI
Description:
Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 118.254.9.44]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:

I have disabled the "sa" account. I also turn off the sql ports of firewall.  How someone try to logon the server? Do you have any ideas?

Thanks,

Stephen

---

stephen

Answer 7

The login failures indicates that someone is knocking at the door, but the SQL injection comes from a poorly implemented application. It's not coming from someone who is trying to login.

Is there a web site connected to this server instance? That would be the main suspect.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Hi Stephenwhx,

Check for the following:

1) Restart the SQL Server in single user mode. Click the link following link on how to start SQL Server in single user mode:

http://msdn.microsoft.com/en-us/library/ms188236.aspx

2) Launch SSMS and execute the following query to find the startup procedures. You can use the following query for that:

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
DECLARE @procid INT
CREATE TABLE #tmp(objectname varchar(100),startup_status varchar(50)) 
DECLARE proc_cursor CURSOR FOR SELECT id FROM master.dbo.sysobjects WHERE TYPE IN ('P','X') 
OPEN proc_cursor  
FETCH NEXT FROM proc_cursor INTO @procid 
WHILE @@FETCH_STATUS = 0 
BEGIN
INSERT #tmp   
SELECT OBJECT_NAME(@procid),CASE Objectproperty(@procid,'ExecIsStartup') WHEN 1 then 'Enabled' else 'Disabled' end
FETCH NEXT FROM proc_cursor INTO @procid 
END
CLOSE proc_cursor 
DEALLOCATE proc_cursor 
SELECT * FROM #tmp WHERE startup_status = 'Enabled'
DROP TABLE #tmp 

3) Examine your SQL Server Error for anything that looks daughy.

4) Enable Logon Auding and Common Compliance Critera. You can do this by right-clicking SQL Server and Choose Properties and Then Activate Security tab. 

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Hi Basit,

I have tried your solution, but it cannot find anything from the query. Do you have any other ideas?

Thanks,

---

stephen

Answer 9

YEs, one web application connects into the SQL instance. how to find the "running code- sql injection". Otherwise, if I restore the DB, it will be hacked again.

Thanks,

---

stephen

Answer 10

YEs, one web application connects into the SQL instance. how to find the "running code- sql injection". Otherwise, if I restore the DB, it will be hacked again.

You need to find a developer that can review the application code. And, unfortunately, there is a risk that the code typically looks like this:

sSQL = "SELECT ... FROM tbl WHERE col = '" + value + "'"

Which means that the application needs a major overhaul.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

Hi Stephenwhx,

Have you done the following steps which I mentioned in my post:

3) Examine your SQL Server Error for anything that looks daughy.

4) Enable Logon Auding and Common Compliance Critera. You can do this by right-clicking SQL Server and Choose Properties and Then Activate Security tab. 

If so, then please examine your log file for all queries and commands executed by your application. Identify the command and user from log files generated to identify the malicious actives and then reset the user password. You also need to look from where this command is issued and then fix the problem.

Unfortunately there is no other way to investigate.

Hope you will identify the root cause like that.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 12

Yes, I did steps 3 and 4> but, I cannot find anything.

---

stephen

Answer 13

If you can't find any thing suspicious in the Log then this means that there is no SQL Injection. If you enable Common Compliance Criteria option, then every activity on the server will be captured. For more information, see http://msdn.microsoft.com/en-us/library/bb326650.aspx.

Unfortunately, I got no access to your SQL Server. I suggest you to read on Common Compliance Criteria and then review the log.

Also execute the following query to investigate what has been executed on your SQL Server recently.

SELECT deqs.last_execution_time AS [Time], 
dest.TEXT AS [Query], *
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time ASC

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 14

Hi Basit,

I did get over 400 results by running your query, how do I know which one has problem. I am not a developer of the web application.

Thanks,

---

stephen

Answer 15

I did get over 400 results by running your query, how do I know which one has problem. I am not a developer of the web application.

Queries that follows this pattern are good:

(@orderid int)SELECT * FROM Orders WHERE OrderID = @orderid

That it, there is a parameter list in parathesis in the beginning of the query. If they start with CREATE PROCEDURE, they are also good.

On the other hand if the query looks like:

   SELECT * FROM Orders WHERE OrderID = 11000

That is, there is a parameter value directly in the query string, that is bad. Most likely you have a bunch of these, and all are bad. Which the intruder used to get in is not of interest. If you close that hole, the intruder will find the next.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 16

I did get over 400 results by running your query, how do I know which one has problem. I am not a developer of the web application.

Queries that follows this pattern are good:

(@orderid int)SELECT * FROM Orders WHERE OrderID = @orderid

That it, there is a parameter list in parathesis in the beginning of the query. If they start with CREATE PROCEDURE, they are also good.

On the other hand if the query looks like:

   SELECT * FROM Orders WHERE OrderID = 11000

That is, there is a parameter value directly in the query string, that is bad. Most likely you have a bunch of these, and all are bad. Which the intruder used to get in is not of interest. If you close that hole, the intruder will find the next.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

I have check log by "dbcc log" 

there are many allocunitname like "dbo.takeaway.PK_takeaway"

Is it possible to find out form the db log?

Thanks

---

stephen

Answer 17

I have check log by "dbcc log" 

there are many allocunitname like "dbo.takeaway.PK_takeaway"

Is it possible to find out form the db log?

Not really. Say that the bad query is:

 sSQL = "SELECT .. FROM tbl WHERE cookie = '" & cookie & "'"

The malicious user makes his web proxy to return

   '; INSERT tbl(col) VALUES ('somebaddata') --

You have no choice but call for a general review of the application code.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 18

all results are here:

can you see anything wrong?

SELECT schedule_id, schedule_uid, name, enabled, freq_type, freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, active_start_date, active_end_date, active_start_time, active_end_time FROM msdb.dbo.sysschedules sch WHERE (EXISTS(SELECT * FROM sysjobschedules as jobsch WHERE sch.schedule_id = jobsch.schedule_id))

SELECT         CASE WHEN OBJECT_NAME(c.classifier_function_id) IS NULL THEN N''  ELSE QUOTENAME(OBJECT_SCHEMA_NAME(c.classifier_function_id, 1)) + N'.' + QUOTENAME(OBJECT_NAME(c.classifier_function_id, 1))  END       AS [ClassifierFunction], CAST(c.is_enabled AS bit) AS [Enabled], CAST((SELECT is_reconfiguration_pending FROM sys.dm_resource_governor_configuration) AS bit) AS [ReconfigurePending] FROM sys.resource_governor_configuration AS c

SELECT case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state  where target_query_expression_with_id like 'Server%' ) then 1 else 0 end AS [PolicyHealthState]

SELECT CAST(serverproperty(N'Servername') AS sysname) AS [Name], 'CollectorConfigStore[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' AS [Urn] ORDER BY [Name] ASC

SELECT CAST(serverproperty(N'Servername') AS sysname) AS [Name], 'PolicyStore[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' AS [Urn] ORDER BY [Name] ASC

SELECT CAST(serverproperty(N'Servername') AS sysname) AS [Name], 'Server[@Name=' + quotename(CAST(          serverproperty(N'Servername')         AS sysname),'''') + ']' + '/JobServer' AS [Urn] ORDER BY [Name] ASC

select category_id from msdb.dbo.sysjobs_view where job_id='c99dd967-7be2-44fb-98b9-dcf47907b5eb'

SELECT category_id, category_type, name FROM msdb.dbo.syscategories WHERE (category_class = 1) ORDER BY category_type, name

SELECT cfg.name AS [Name], cfg.configuration_id AS [Number], cfg.minimum AS [Minimum], cfg.maximum AS [Maximum], cfg.is_dynamic AS [Dynamic], cfg.is_advanced AS [Advanced], cfg.value AS [ConfigValue], cfg.value_in_use AS [RunValue], cfg.description AS [Description] FROM sys.configurations AS cfg

SELECT class.class_num, class.takeaway, class.class_date, [section].class_time FROM class INNER JOIN [section] ON class.section_id = [section].section_id WHERE [section].section_id = 129 AND class.class_date >= '8/17/2012' ORDER BY class.class_date

select col.name,  st.name as DT_name,   case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0))  then col.max_length / 2 else col.max_length end, col.precision,  col.scale,  bt.name as BT_name,  col.is_nullable,  col.is_identity,col.is_rowguidcol,  OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,   CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed,  case when(cmc.column_id is null) then null else cmc.definition end as formular,  col.collation_name,  col.system_type_id from [2400_main].sys.all_columns col  left outer join [2400_main].sys.types st on st.user_type_id = col.user_type_id left outer join [2400_main].sys.types bt on bt.user_type_id = col.system_type_id  left outer join [2400_main].sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id  left outer join [2400_main].sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id  where col.object_id = object_id(N'[2400_main].dbo.instructor')  order by col.column_id

select count(*) from (select distinct [InstallationID] from [keys] where [SymmetricKey] is not null and [client] >= 0) as A

select count(*) from Schedule

SELECT deqs.last_execution_time AS [Time],   dest.TEXT AS [Query], *  FROM sys.dm_exec_query_stats AS deqs  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest  ORDER BY deqs.last_execution_time ASC

SELECT deqs.last_execution_time AS [Time],   dest.TEXT AS [Query], *  FROM sys.dm_exec_query_stats AS deqs  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest  ORDER BY deqs.last_execution_time ASC    dbcc log ([2400_main],1)

SELECT deqs.last_execution_time AS [Time],   dest.TEXT AS [Query], *  FROM sys.dm_exec_query_stats AS deqs  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest  ORDER BY deqs.last_execution_time ASC    dbcc log ([2400_main],4)

SELECT dtb.name AS [Name], CAST(isnull(dtb.source_database_id, 0) AS bit) AS [IsDatabaseSnapshot],                 case                when DATABASEPROPERTY(dtb.name,'IsShutDown') is null then 0x200                else 0                end |                case                when 1 = dtb.is_in_standby then 0x40                else 0                end |                case                when 1 = dtb.is_cleanly_shutdown then 0x80                else 0                end |                case dtb.state                when 1 then 0x2                when 2 then 0x8                when 3 then 0x4                when 4 then 0x10                when 5 then 0x100                when 6 then 0x20                else 1                end               AS [Status], dtb.compatibility_level AS [CompatibilityLevel], dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb ORDER BY [Name] ASC

SELECT dtb.name AS [Name], dtb.database_id AS [ID], CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject], CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible] FROM master.sys.databases AS dtb ORDER BY [Name] ASC

SELECT dtb.name AS [Name], dtb.database_id AS [ID], CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject], CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible] FROM master.sys.databases AS dtb ORDER BY [Name] ASC

SELECT dtb.name AS [Name], dtb.database_id AS [ID], CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject], CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible], dtb.collation_name AS [Collation], dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb ORDER BY [Name] ASC

SELECT health_state_id, target_query_expression_with_id FROM msdb.dbo.syspolicy_system_health_state

SELECT iItem FROM items WHERE (iTarget = 'A' OR iTarget = 'X') AND iTerm_id = 120 ORDER BY iID DESC

SELECT job_id, schedule_id, next_run_date, next_run_time FROM sysjobschedules ORDER BY job_id

SELECT log.name AS [Name], log.principal_id AS [ID], ISNULL(ak.name,N'') AS [AsymmetricKey], ISNULL(cert.name,N'') AS [Certificate], ISNULL(c.name,N'') AS [Credential], CASE WHEN N'U' = log.type THEN 0 WHEN N'G' = log.type THEN 1 WHEN N'S' = log.type THEN 2 WHEN N'C' = log.type THEN 3 WHEN N'K' = log.type THEN 4 END AS [LoginType] FROM sys.server_principals AS log LEFT OUTER JOIN master.sys.asymmetric_keys AS ak ON ak.sid = log.sid LEFT OUTER JOIN master.sys.certificates AS cert ON cert.sid = log.sid LEFT OUTER JOIN sys.credentials AS c ON c.credential_id = log.credential_id WHERE (log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##') ORDER BY [Name] ASC

SELECT sa.id,                 sa.name,                      sa.event_source,                      sa.event_category_id,                      sa.event_id,                      sa.message_id,                      sa.severity,                      sa.enabled,                      sa.delay_between_responses,                      sa.last_occurrence_date,                      sa.last_occurrence_time,                      sa.last_response_date,                      sa.last_response_time,                      sa.notification_message,                      sa.include_event_description,                      sa.database_name,                      sa.event_description_keyword,                      sa.occurrence_count,                      sa.count_reset_date,                      sa.count_reset_time,                      sjv.job_id,                      job_name = sjv.name,                      sa.has_notification,                      sa.flags,                      sa.performance_condition,                      category_
name = sc.name,                      type = CASE ISNULL(sa.performance_condition, '!')                    WHEN '!' THEN 1            -- SQL Server event alert                    ELSE CASE sa.event_id                       WHEN 8 THEN 4          -- WMI event alert                       ELSE 2                    -- SQL Server performance condition alert                    END                 END               FROM msdb.dbo.sysalerts                     sa                    LEFT OUTER JOIN msdb.dbo.sysjobs_view  sjv ON (sa.job_id = sjv.job_id)                    LEFT OUTER JOIN msdb.dbo.syscategories sc  ON (sa.category_id = sc.category_id)               WHERE ((N'' = N'') OR (sa.name = N''))                 AND ((NULL IS NULL) OR (sa.id = NULL))                 AND ((N'' = N'') OR (sc.name = N''))               ORDER BY event_id DESC, severity ASC, message_id ASC, database_name DESC

SELECT satypes.name AS [Schema], atypes.name AS [Name] FROM sys.assembly_types AS atypes INNER JOIN sys.assemblies AS asmbl ON (asmbl.assembly_id = atypes.assembly_id) and (atypes.is_user_defined = 1) INNER JOIN sys.schemas AS satypes ON satypes.schema_id = atypes.schema_id ORDER BY [Schema] ASC,[Name] ASC

SELECT satypes.name AS [Schema], atypes.name AS [Name] FROM sys.assembly_types AS atypes INNER JOIN sys.assemblies AS asmbl ON (asmbl.assembly_id = atypes.assembly_id) and (atypes.is_user_defined = 1) INNER JOIN sys.schemas AS satypes ON satypes.schema_id = atypes.schema_id ORDER BY [Schema] ASC,[Name] ASC

SELECT sch.name, sn.name,  sn.base_object_name,   CASE     WHEN ObjectPropertyEx(sn.object_id, 'BaseType') = 'U' THEN 2      WHEN ObjectPropertyEx(sn.object_id, 'BaseType') = 'V' THEN 3      WHEN ObjectPropertyEx(sn.object_id, 'BaseType') IN ('FT', 'TF') THEN 4      WHEN ObjectPropertyEx(sn.object_id, 'BaseType') = 'IF' THEN 5      WHEN ObjectPropertyEx(sn.object_id, 'BaseType') = 'AF' THEN 6      ELSE 0  END  FROM  sys.synonyms AS sn INNER JOIN          sys.schemas AS sch ON sch.schema_id = sn.schema_id  WHERE (select case when object_id(base_object_name) IS NULL Then 'P'   else ObjectPropertyEx(object_id, 'BaseType')  end from sys.synonyms  where  object_id = sn.object_id) NOT IN ('FN', 'FS', 'P')

SELECT SCHEMA_NAME(obj.schema_id) AS [Schema], obj.name AS [Name], obj.object_id AS [ID], usrt.name AS [DataType], ISNULL(baset.name, N'') AS [SystemType], CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND ret_param.max_length <> -1 THEN ret_param.max_length/2 ELSE ret_param.max_length END AS int) AS [Length], CAST(ret_param.precision AS int) AS [NumericPrecision], CAST(ret_param.scale AS int) AS [NumericScale], ISNULL(xscret_param.name, N'') AS [XmlSchemaNamespace], ISNULL(s2ret_param.name, N'') AS [XmlSchemaNamespaceSchema], ISNULL( (case ret_param.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint], sret_param.name AS [DataTypeSchema] FROM sys.objects AS obj LEFT OUTER JOIN sys.all_parameters AS ret_param ON ret_param.object_id = obj.object_id and ret_param.is_output = 1 LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = ret_param.user_type_id LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = ret_param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = ret_param.system_type_id) and (baset.user_type_id = ret_param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))  LEFT OUTER JOIN sys.xml_schema_collections AS xscret_param ON xscret_param.xml_collection_id = ret_param.xml_collection_id LEFT OUTER JOIN sys.schemas AS s2ret_param ON s2ret_param.schema_id = xscret_param.schema_id LEFT OUTER JOIN sys.schemas AS sret_param ON sret_param.schema_id = usrt.schema_id WHERE (obj.type=N'AF') ORDER BY [Schema] ASC,[Name] ASC

SELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.name AS [Name] FROM sys.tables AS tbl ORDER BY [Schema] ASC,[Name] ASC

SELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.name AS [Name] FROM sys.tables AS tbl ORDER BY [Schema] ASC,[Name] ASC

SELECT SCHEMA_NAME(tt.schema_id) AS [Schema], tt.name AS [Name] FROM sys.table_types AS tt INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id ORDER BY [Schema] ASC,[Name] ASC

SELECT SCHEMA_NAME(tt.schema_id) AS [Schema], tt.name AS [Name] FROM sys.table_types AS tt INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id ORDER BY [Schema] ASC,[Name] ASC

SELECT SCHEMA_NAME(xproc.schema_id) AS [Schema], xproc.name AS [Name], xproc.object_id AS [ID], CAST(                  xproc.is_ms_shipped              AS bit) AS [IsSystemObject] FROM sys.all_objects AS xproc WHERE (xproc.type='X') ORDER BY [Schema] ASC,[Name] ASC

SELECT SCHEMA_NAME(xproc.schema_id) AS [Schema], xproc.name AS [Name], xproc.object_id AS [ID], CAST(                  xproc.is_ms_shipped              AS bit) AS [IsSystemObject] FROM sys.all_objects AS xproc WHERE (xproc.type='X') ORDER BY [Schema] ASC,[Name] ASC

SELECT 'Server[@Name=' + quotename(CAST(          serverproperty(N'Servername')         AS sysname),'''') + ']' + '/ResourceGovernor' AS [Urn], case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like 'Server/ResourceGovernor%' ESCAPE '\') then 1 else 0 end AS [PolicyHealthState] FROM sys.resource_governor_configuration AS c ORDER BY CAST(c.is_enabled AS bit) ASC

SELECT sst.name AS [Schema], st.name AS [Name] FROM sys.types AS st INNER JOIN sys.schemas AS sst ON sst.schema_id = st.schema_id WHERE (st.schema_id!=4 and st.system_type_id!=240 and st.user_type_id != st.system_type_id and st.is_table_type != 1) ORDER BY [Schema] ASC,[Name] ASC

SELECT sst.name AS [Schema], st.name AS [Name] FROM sys.types AS st INNER JOIN sys.schemas AS sst ON sst.schema_id = st.schema_id WHERE (st.schema_id!=4 and st.system_type_id!=240 and st.user_type_id != st.system_type_id and st.is_table_type != 1) ORDER BY [Schema] ASC,[Name] ASC

SELECT su.name ,so.name , isr.DATA_TYPE  FROM           sysobjects so INNER JOIN  sysusers su           ON so.uid = su.uid           INNER JOIN INFORMATION_SCHEMA.ROUTINES isr           ON so.name = isr.SPECIFIC_NAME           WHERE  so.type = N'AF'

SELECT sv.name AS [Name], sv.category_id AS [CategoryID], sv.job_id AS [JobID] FROM msdb.dbo.sysjobs_view AS sv ORDER BY [Name] ASC

SELECT TOP (200) prof_id, first_name, last_name, office, phone, email, hours FROM instructor

select value_in_use from sys.configurations where configuration_id = 16384

Thanks,

Answer 19

Most of these queries are from SQL Server Agent, Policy Based Management or metadata queries from SSMS. And if you have the database off the hook, it is not surprising that there are no queries from the application in the cache.

But this one seems to be an application query:

SELECT class.class_num, class.takeaway, class.class_date, [section].class_time FROM class INNER JOIN [section] ON class.section_id = [section].section_id WHERE [section].section_id = 129 AND class.class_date >= '8/17/2012' ORDER BY class.class_date

This is exactly what I was suspecting, and this is exactly how an application should not construct it's query. It is unlikely that the date is hardcoded in the query, but it comes from user input. And if it is a field where the user can type text, this is a query that could be used for SQL injection.

But it is likely to be just one of many such queries.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 20

I did not look at the code yet,

but I believe that the "129" and '8/17/2012'  are generated by variables

Do you think that the variables can generate the SQL injection problem?

Thanks

---

stephen

Answer 21

I did not look at the code yet,

but I believe that the "129" and '8/17/2012'  are generated by variables



Do you think that the variables can generate the SQL injection problem?

Yes, they can, if not handled properly. In this case they are not.

The code behind the query look something like this:

sSQL = "SELECT class.class_num, class.takeaway, class.class_date, " &_
       "[section].class_time FROM class INNER JOIN [section] ON " &_
       "class.section_id = [section].section_id WHERE " &_
       "[section].section_id = " & section_id & _
       "AND class.class_date >= '" & class_date & "'" & _
       "ORDER BY class.class_date"

It should look like this:

sSQL = "SELECT class.class_num, class.takeaway, class.class_date, " &_
       "[section].class_time FROM class INNER JOIN [section] ON " &_
       "class.section_id = [section].section_id WHERE " &_
       "[section].section_id = ? AND class.class_date >= ? " & _
       "ORDER BY class.class_date"
cmd.CommandText = sSQL
cmd.Parameters.Append cmd.CreateParameter("@sectionid", _
                         adInt, adParamInput, , section_id)
cmd.Parameters.Append cmd.CreateParameter("@class_date", _
                         adDateTime, adParamInput, , class_date)

Of course, if the ASP variabels section_id and class_date are populated from something else than user input, for instance by lookup in a database table, this particular query is safe. However, the query is still bad, because for each new value of the variables there will be a new cache entry. And if the dateformat settings would change in the server, SQL Server interpret the string '8/17/2012' differently. When you use parametermeterised statements as I showed above, these issues do not arise, as that is cached is the plan for the parmeterised query.

And there is no reason to assume that this query is the only bad query in the system. Au contraire, my experience tells me that developers have more or less consistently used this bad pattern. And it is exactly this pattern that makes SQL injection possible. With parameterised queries, there is nothing to inject into.

You are certainly in a major challenge. Maybe some developers are better than the guy who wrote this query. But you (or rathre your organisation) still need to review each and every single query that the web site produces and make sure that variables are passed as parameters, and not built into the query string.

On the flip side, the performance in SQL Server will benefit from the overhaul.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 22

Thanks very much.

I will ask the developer to make this change, and see whether the problem can be fixed.

Stephen

---

stephen