{"id":2581,"date":"2014-10-02T11:00:07","date_gmt":"2014-10-02T15:00:07","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2581"},"modified":"2014-11-26T16:58:03","modified_gmt":"2014-11-26T21:58:03","slug":"defects","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2581\/defects\/","title":{"rendered":"Common Database Vulnerabilities: Defects"},"content":{"rendered":"
\n

\nIf you have been working in (or close to) the database field for a while, you have probably heard of or seen a defect caused by a missing WHERE<\/span> clause. This type of defect is very common and often leads to data corruption.\n<\/p>\n

\n\"software<\/a>\n<\/div>\n

\nA good friend of mine, just having started out on his first job out of college, did what you should expect of any good employee. He was working on familiarizing himself with the system the company developed, which in his case meant reading the code. What he ran across there startled him. He found a WHERE<\/span> clause on an UPDATE<\/span> statement that did not make sense.\n<\/p>\n

\nLong story short, after reviewing the code with a more senior colleague, they determined that this UPDATE<\/span> statement, which was supposed to update a single order, in some cases updated a bunch of orders. The system was the in-house order system of the company itself and they used it for a product type with a long lead cycle. Between order placement and delivery often several months passed. While they had noticed an oddly high number of \"I did not order this\" complaints from customers, because of the long feedback loop they were not able to tie it to the order system, particularly as the misguided UPDATE<\/span> always updated the correct record. It just also updated a few unrelated orders.\n<\/p>\n

The missing WHERE clause<\/h3>\n

\nSo, you might ask, what does this have to do with security? In the above case, the missing (part of the) WHERE<\/span> clause caused tremendous damage to the business, but this was not a security concern. In other cases however, a defect like a missing WHERE<\/span> clause section might cause data to be shown to a user that they were not supposed to see.\n<\/p>\n

\nImagine, for example, that your shopping cart would start allowing customers to pay with other customers' credit cards. This would certainly be a disaster. Cases like this, involving customers being able to access other customers' data, are actually quite common.\n<\/p>\n

Avoid Defects with Test Driven Design<\/h3>\n

\nOne of the ways to prevent defects like this is to use Test Driven Design as a development methodology. The main purpose of Test Driven Design is actually to guide us to well-designed software. However, using this methodology tends to lead to a code base that is well tested and does not contain edge-case-disasters like missing parts of WHERE<\/span> clauses.\n<\/p>\n

\nI am not saying the Test Driven Design is the only solution to this problem. However, in my experience Test Driven Design is a very powerful methodology that if implemented correctly, tends to result in a significantly reduced defect count. As an added benefit, you will get a lot more confident in your code and changes will be much simpler to implement.\n<\/p>\n

tSQLt<\/h3>\n

\ntSQLt, an open source unit testing framework for SQL Server allows you to write your database code using Test Driven Design. tSQLt is similar to other unit testing frameworks like NUnit or Junit and it is available for free at tSQLt.org<\/a>.\n<\/p>\n

\nYou can find an introduction to tSQLt on that website and an introduction to Test Driven Design in this article<\/a> that I wrote a while back for SQL Server Pro.\n<\/p>\n

\nAs always, the time to act is now. Do not wait until someone else discovers or even exploits a defect-caused vulnerability in your code base. Be proactive about it; implement good coding and design techniques. Start by downloading tSQLt<\/a> now.\n<\/p>\n

The Most Common Database Problems<\/h3>\n

\nDefects are one of the most commonly encountered database vulnerabilities. In this series of posts, I discuss 10 of them. Below are the ones that are published so far:\n<\/p>\n

\n
\n

Common Database Vulnerabilities: Privilege Abuse<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: SQL Injection<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: Unprotected Network<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: Inadequate Patch Management<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: Backup Data Exposure<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: Missing Segregation<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: Flawed Key Management<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: Weak Audit Trail<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: Data Inference<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Common Database Vulnerabilities: Defects<\/a><\/h2>\n
<\/div>\n<\/div>\n<\/div>\n\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Defects are a nuisance. However, they often lead to bigger problems. You might find your code corruption data by updating the wrong records or opening up gaping security holes allowing users to see other users’ data. Find out how to protect yourself from this danger.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2583,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[222,5,34,27,25,12],"tags":[38,15,272,239,112],"class_list":["post-2581","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-common-database-vulnerabilities","category-general","category-security","category-series","category-tdd","category-tsqlt","tag-security-2","tag-sql-server","tag-tdd","tag-test-driven-design","tag-vulnerability"],"acf":[],"yoast_head":"\nCommon Database Vulnerabilities: Defects - sqlity.net<\/title>\n<meta name=\"description\" content=\"Defects are a nuisance. However, they often lead to bigger problems like security vulnerabilities. Find out how to protect yourself from this danger.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2581\/defects\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Common Database Vulnerabilities: Defects - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Defects are a nuisance. However, they often lead to bigger problems like security vulnerabilities. Find out how to protect yourself from this danger.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2581\/defects\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-10-02T15:00:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-26T21:58:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/10\/software-defects.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"375\" \/>\n\t<meta property=\"og:image:height\" content=\"450\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Common Database Vulnerabilities: Defects\",\"datePublished\":\"2014-10-02T15:00:07+00:00\",\"dateModified\":\"2014-11-26T21:58:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/\"},\"wordCount\":661,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/10\\\/software-defects.jpg\",\"keywords\":[\"security\",\"SQL Server\",\"TDD\",\"Test Driven Design\",\"vulnerability\"],\"articleSection\":[\"Common Database Vulnerabilities\",\"General\",\"Security\",\"Series\",\"TDD\",\"tSQLt\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/\",\"name\":\"Common Database Vulnerabilities: Defects - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/10\\\/software-defects.jpg\",\"datePublished\":\"2014-10-02T15:00:07+00:00\",\"dateModified\":\"2014-11-26T21:58:03+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Defects are a nuisance. However, they often lead to bigger problems like security vulnerabilities. Find out how to protect yourself from this danger.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/10\\\/software-defects.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/10\\\/software-defects.jpg\",\"width\":375,\"height\":450},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2581\\\/defects\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Common Database Vulnerabilities: Defects\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Common Database Vulnerabilities: Defects - sqlity.net","description":"Defects are a nuisance. However, they often lead to bigger problems like security vulnerabilities. Find out how to protect yourself from this danger.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/2581\/defects\/","og_locale":"en_US","og_type":"article","og_title":"Common Database Vulnerabilities: Defects - sqlity.net","og_description":"Defects are a nuisance. However, they often lead to bigger problems like security vulnerabilities. Find out how to protect yourself from this danger.","og_url":"https:\/\/sqlity.net\/en\/2581\/defects\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-10-02T15:00:07+00:00","article_modified_time":"2014-11-26T21:58:03+00:00","og_image":[{"width":375,"height":450,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/10\/software-defects.jpg","type":"image\/jpeg"}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2581\/defects\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2581\/defects\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Common Database Vulnerabilities: Defects","datePublished":"2014-10-02T15:00:07+00:00","dateModified":"2014-11-26T21:58:03+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2581\/defects\/"},"wordCount":661,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2581\/defects\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/10\/software-defects.jpg","keywords":["security","SQL Server","TDD","Test Driven Design","vulnerability"],"articleSection":["Common Database Vulnerabilities","General","Security","Series","TDD","tSQLt"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2581\/defects\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2581\/defects\/","url":"https:\/\/sqlity.net\/en\/2581\/defects\/","name":"Common Database Vulnerabilities: Defects - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2581\/defects\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2581\/defects\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/10\/software-defects.jpg","datePublished":"2014-10-02T15:00:07+00:00","dateModified":"2014-11-26T21:58:03+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Defects are a nuisance. However, they often lead to bigger problems like security vulnerabilities. Find out how to protect yourself from this danger.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2581\/defects\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2581\/defects\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2581\/defects\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/10\/software-defects.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/10\/software-defects.jpg","width":375,"height":450},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2581\/defects\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Common Database Vulnerabilities: Defects"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/10\/software-defects.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/s2wXuw-defects","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2581","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2581"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2581\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2583"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}