Tuesday 30 March 2010

Lines of SQL in stored procedures

If you want to count the number of lines of SQL in stored procedures and functions try running the following SQL:

USE [Database name here]

SELECT  t.sp_name,
        SUM(t.lines_of_code) - 1 AS lines_ofcode,
        t.type_desc
FROM    ( SELECT    o.name AS sp_name,
                    ( LEN(c.text) - LEN(REPLACE(c.text, CHAR(10), '')) ) AS lines_of_code,
                    CASE WHEN o.xtype = 'P' THEN 'Stored Procedure'
                         WHEN o.xtype IN ( 'FN', 'IF', 'TF' ) THEN 'Function'
                    END AS type_desc
          FROM      sysobjects o
                    INNER JOIN syscomments c ON c.id = o.id
          WHERE     o.xtype IN ( 'P', 'FN', 'IF', 'TF' )
                    AND o.category = 0
                    AND o.name NOT IN ( 'fn_diagramobjects', 'sp_alterdiagram',
                                        'sp_creatediagram', 'sp_dropdiagram',
                                        'sp_helpdiagramdefinition',
                                        'sp_helpdiagrams', 'sp_renamediagram',
                                        'sp_upgraddiagrams', 'sysdiagrams' )
        ) t
GROUP BY t.sp_name,
        t.type_desc
ORDER BY 1

Thursday 11 March 2010

ISAPI_Rewrite and SQL injection

The following rules might help out in protecting a site against SQL injection:

[ISAPI_Rewrite]
RewriteRule .*(?:global.asa|default\.ida|root\.exe|\.\.).* . [F,I,O]

RewriteRule .*(?:DECLARE).* /null.htm [F,I]

RewriteRule ^.*\+update\+.*$ /null.htm [F,I]
RewriteRule ^.*SUBSTRING\(.*$ /null.htm [F,I]
RewriteRule ^.*CHARINDEX.*$ /null.htm [F,I]
RewriteRule ^.*NVARCHAR.*$ /null.htm [F,I]
RewriteRule ^.*CHAR\(.*$ /null.htm [F,I]
RewriteRule ^.*CAST\(.*$ /null.htm [F,I]
RewriteRule ^.*%20xp_.*$ /null.htm [F,I]
RewriteRule ^.*%20@.*$ /null.htm [F,I]
RewriteRule ^.*@%20.*$ /null.htm [F,I]
RewriteRule ^.*';*$ /null.htm [F,I]
RewriteRule ^.*EXEC\(@.*$ /null.htm [F,I]
RewriteRule ^.*sp_password.*$ /null.htm [F,I]

F – forbidden
I – Ignore case

Wednesday 10 March 2010

Why user NOCOUNT?

Setting NOCOUNT to ON stops the message that shows the count of the number of rows affected by a T-SQL statement or stored procedure from being returned as part of the result set.

Why is this useful or important? It can result in a significant performance boost when running SQL statements:

“When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.” – from SQL Server online documentation