{"id":532,"date":"2009-06-30T08:34:00","date_gmt":"2009-06-30T08:34:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Looking-for-security-vulnerabilities-in-database-code.aspx"},"modified":"2013-01-11T23:11:05","modified_gmt":"2013-01-12T07:11:05","slug":"looking-for-security-vulnerabilities-in-database-code","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/looking-for-security-vulnerabilities-in-database-code\/","title":{"rendered":"Looking for security vulnerabilities in database code"},"content":{"rendered":"

\nI've always been concerned with security and I've always stressed the importance of auditing the REAL user context not just the current user (see this post<\/a> on EXECUTE AS and auditing). So, I generally try to avoid using dynamic string execution and if necessary create well tested\/protected parameters (fyi – using QUOTENAME can be a fantasic solution to protectng identifiers as input parameters but it can't protect more complex strings).\n<\/p>\n

\nHaving said that, what if I'm looking at a database for the first time… just poking around trying to see if there's anything that needs further attention? I've come up with a quick query… And, while it's not going to "solve" your problem (as that's going to take some re-writing of code) or even truly <\/em>verify if you're vulnerable, it gives you a "quick list" of where you should look first! If your code uses dynamic strings AND it's elevated – then start there! \n<\/p>\n

<\/p>\n

\n

\n\tSELECT OBJECT_NAME(object_id) AS [Procedure Name],
\n\t  CASE
\n\t      WHEN sm.definition LIKE '%EXEC (%' OR sm.definition LIKE '%EXEC(%' THEN 'WARNING: code contains EXEC'
\n\t      WHEN sm.definition LIKE '%EXECUTE (%' OR sm.definition LIKE '%EXECUTE(%' THEN 'WARNING: code contains EXECUTE'
\n\t  END AS [Dynamic Strings],
\n\t  CASE
\n\t      WHEN execute_as_principal_id IS NOT NULL THEN N'WARNING: EXECUTE AS ' + user_name(execute_as_principal_id)
\n\t      ELSE 'Code to run as caller – check connection context'
\n\t  END AS [Execution Context Status]
\n\tFROM sys.sql_modules AS sm
\n\tORDER BY [Procedure Name]\n\t<\/p>\n<\/blockquote>\n

\nIs this enough? Anything else you'd check? What do you think?\n<\/p>\n

\nTHANKS!
\nkt<\/p>\n","protected":false},"excerpt":{"rendered":"

I've always been concerned with security and I've always stressed the importance of auditing the REAL user context not just the current user (see this post on EXECUTE AS and auditing). So, I generally try to avoid using dynamic string execution and if necessary create well tested\/protected parameters (fyi – using QUOTENAME can be a […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,59,62,65,66],"tags":[],"class_list":["post-532","post","type-post","status-publish","format-standard","hentry","category-dynamic-string-execution","category-security","category-sp_executesql","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/532","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=532"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/532\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=532"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=532"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=532"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}