{"id":1000529,"date":"2007-04-06T10:13:28","date_gmt":"2007-04-06T15:13:28","guid":{"rendered":"http:\/\/www.elharo.com\/blog\/software-development\/databases\/2007\/04\/06\/fundamentally-safe-database-apis\/"},"modified":"2007-04-06T13:16:38","modified_gmt":"2007-04-06T18:16:38","slug":"fundamentally-safe-database-apis","status":"publish","type":"post","link":"https:\/\/www.elharo.com\/blog\/software-development\/databases\/2007\/04\/06\/fundamentally-safe-database-apis\/","title":{"rendered":"Fundamentally Safe Database APIs"},"content":{"rendered":"

Is anyone aware of work on fundamentally safe database APIs for server side programs that completely avoid the possibility of SQL injection? What I envision is a somewhat limited API that does not execute raw SQL statements or provide any facility to do so. Instead you’d set up something like this:<\/p>\n

Statement s = database.getSelectStatement();\r\ns.setTable(\"customers\");\r\ns.addField(\"email\");\r\ns.addField(\"telephone\");\r\ns.addCondition(\r\n  new EqualsCondition(\"id\", \"p17\")\r\n);\r\nResultSet r = s.execute();\r\n<\/code><\/pre>\n
The library would turn this into the usual SQL statement<\/p>\n
SELECT email, telephone FROM customers WHERE id = \"p17\"<\/code><\/p>\n
The library could verify the individual parts of the query before submitting it to the database. If you passed a string like \"id = \\\"p17\\\" OR true; DELETE * FROM customers; SELECT * FROM customers WHERE \"<\/code> to EqualsCondition()<\/code> it would throw an exception.
\n<\/p>\n
This API doesn’t have to be in Java or any particular language. Nor does it have to have the full power of SQL. Basic SELECTs would be sufficient. Safety is valued over power here.<\/p>\n
Surely I’m not the first person to think of this? Are there any APIs of this nature? in any environment or language?<\/p>\n","protected":false},"excerpt":{"rendered":"
Is anyone aware of work on fundamentally safe database APIs for server side programs that completely avoid the possibility of SQL injection? What I envision is a somewhat limited API that does not execute raw SQL statements or provide any facility to do so. Instead you’d set up something like this: Statement s = database.getSelectStatement(); […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[410],"class_list":["post-1000529","post","type-post","status-publish","format-standard","hentry","category-databases","tag-flash"],"_links":{"self":[{"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/posts\/1000529","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/comments?post=1000529"}],"version-history":[{"count":0,"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/posts\/1000529\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/media?parent=1000529"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/categories?post=1000529"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.elharo.com\/blog\/wp-json\/wp\/v2\/tags?post=1000529"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}