> I still think the solution to SQL access is to remove support for using
> strings as SQL statements, and instead have a "SQL statement" type, with
> functions to append statement text (with it being an error to include any
> single quotes in this) and to append constants.
I don't think so. There is no intrinsic problem of using strings as SQL statements. It is the same whether you write
handle.do_select("SELECT id, name FROM customers WHERE country='" + country + "'")
handle.do_select(new SQLStatement("SELECT id, name FROM customers WHERE country='" + country + "'")).
If your user don't know what's wrong with it, you can't expect them to do the right thing for it. If the syntax for doing things the secure way is harder than the syntax for doing things the insecure way, you will see lazy people doing the wrong things saying that they are only for the prototype and never correct it when the software is released.
The only answer is a combination of education and making correct things easier. Make the first example in the official database tutorial that needs a user-provided input to read handle.do_select("SELECT id, name FROM customers WHERE country=?", country), and tell learners that this is safe because even if country contains a single quote or semicolon it does the right thing to get them escaped. Build the library so that a single placeholder syntax is used for all different SQL implementations. As a bonus, teach them how to subvert programs that simply appends the user input to the SQL. Then doing it the secure way is easier than doing it the insecure way, and people actually feel insecure when they write insecurely. Then there will be no more problem of SQL injection.
Copyright © 2017, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds