4 ms·
You're attacking the problem from the wrong angle. The fault lies with whomever builds the application /interfacing/ with the DB, not whomever manages the datab
by phlo 13y ago
You're attacking the problem from the wrong angle. The fault lies with whomever builds the application /interfacing/ with the DB, not whomever manages the database.
In an application you may need to read user-selected data from some sort of database. As a simple example, you might accept a user's input of an article ID to fetch said article from a db. That might look something like this:
"SELECT * FROM articles WHERE id = $article_id"
Where $article_id is the input you received from your user. A valid $article_id could for example be "7", an invalid one might be "7 OR 1=1". If the latter value is not escaped, it'd change the statement to read "SELECT * FROM articles WHERE id = 7 OR 1=1, returning all articles.
Any somewhat competent programmer would then check if $article_id contains a value of the expected type (i.e. integer, string, string that looks like an email address, ...) and use an escaping function (in PHP this might be mysql_real_escape_string) to escape any special characters (e.g. turn " into \").
If you're doing things right, you'll use a prepared statement. You'll tell your database driver the format of your query first ("SELECT * FROM articles where id = ?"), then provide the contents for your placeholders (? -> $article_id).
Prepared statements are considered more elegant and comfortable to work with; both approaches are secure when done correctly.
All of this is done by the application developer. Now the DBA only gets to work with the assembled query. How would they be able to tell a valid "OR 1=1" from an injected one?
Nonetheless, your point on holding the responsible party accountable stands -- but it's the developers, not the DBAs.
- jingo 13y agoThank you. This is the answer I was looking for. I assumed (incorrectly) that the person designing the database was also involved in selecting the "prepared statements" or "assembled queries", or was the same person. Now I'm thinking the problem may be more with the people building the interfaces to these SQL databases, and the languages they are using to build them. If that's true, then "SQL injection" seems like less of an SQL-specific problem and more of popular label for a more general "santization of user input" in internet-facing programs problem. That problem is as old as the web. And now we encourage every program to be a web-facing application, hosted in "the cloud". Yikes. Anyway, I think my original comment may indeed be valid: in 200xx, in too many cases, programmer knowledge of escaping and quoting (rules that if I'm not mistaken originated when more people were more familiar with terminals and shells) is inadequate.