{"id":619,"date":"2012-05-15T18:13:50","date_gmt":"2012-05-15T18:13:50","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=619"},"modified":"2012-05-15T18:14:01","modified_gmt":"2012-05-15T18:14:01","slug":"sql-safe-query-functions-smart","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/sql-safe-query-functions-smart\/","title":{"rendered":"SQL safe query functions are smart, but not that smart"},"content":{"rendered":"

After you've been coding for a few months, one of the more important lessons you learn is how to avoid SQL-injection<\/a> attacks. Generally, this is done with the help of a language-specific function, which strips\/escapes\/wraps characters appropriately.<\/p>\n

Using this ColdFusion example, everything works as expected:<\/p>\n

<cfquery name="test">
\n\t  SELECT myfield
\n\t  FROM mytable
\n\t  WHERE myid = <cfqueryparam value="#url.id#">
\n\t<\/cfquery>
\n\t<\/code><\/p>\n

Given that the URL scope pulls from the query string, this value could be manipulated by any malicious user, to wreak havoc on your database. CFQueryParam<\/a>, of course, prevents this from happening.<\/p>\n

But, with safeguards generally come consequences, although they are better than the alternative. Try the query below using cfqueryparam:<\/p>\n

<cfquery name="test">
\n\t  SELECT myfield
\n\t  FROM mytable
\n\t  WHERE myid = <cfqueryparam value="#url.id#">
\n\t  LIMIT <cfqueryparam value="#url.count#">
\n\t<\/cfquery><\/code><\/p>\n

You get an SQL error. This is because cfqueryparam wraps the LIMIT parameter in single quotes. A string is given, where an integer is expected.<\/p>\n

You language may have a way to deal with this, but if not, one method is to use your languages "cast to integer" function.<\/p>\n

By casting url.count to an integer, you would effectively strip any malicious SQL-injection commands.<\/p>\n

But, as stated before, your language may have a way to deal with this. In the case of ColdFusion, if you supply a cfsqltype parameter, it will handle it properly:<\/p>\n

<cfquery name="test">
\n\t  SELECT myfield
\n\t  FROM mytable
\n\t  WHERE myid = <cfqueryparam value="#url.id#" cfqsqltype="CF_SQL_TINYINT">
\n\t<\/cfquery><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

After you've been coding for a few months, one of the more important lessons you learn is how to avoid SQL-injection attacks. Generally, this is…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[34,123],"tags":[],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/619"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=619"}],"version-history":[{"count":2,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/619\/revisions"}],"predecessor-version":[{"id":621,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/619\/revisions\/621"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=619"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}