March 9, 2018 at 8:18 pm
Comments posted to this topic are about the item Troublesome Names
Best wishes,
Phil Factor
March 10, 2018 at 4:22 am
Everything mentioned in the editorial is correct. Yet it is incomplete. It fails to give useful, actionable guidance on the most crucial thing; namely what to do.
Instead of mentioning the many difficult problems with validating names it should mention the one thing which will fix the problem.
Instead of attempting to validate names and block SQL statements, such code should use the parameters mechanism. That way we end up with Mr Null as a proper row in the database along with Mrs Create, Miss Drop and the entire Apostrophe family.
We also eliminate a bunch of complex and difficult to maintain code.
In my experience with these, the biggest problem came when we took on a girl who had no family name. She came from a small village, they had no need of such things so they didn’t bother. I bet she left a trail of broken computer systems behind her.
March 10, 2018 at 7:13 am
Everything mentioned in the editorial is correct. Yet it is incomplete. It fails to give useful, actionable guidance on the most crucial thing; namely what to do.
Exactly what I was going to say. But it's important enough to second it.
March 10, 2018 at 7:30 am
No article on this topic is complete without a link to the famous xkcd cartoon on this subject ("Exploits of a Mom").
https://xkcd.com/327/
In theory, theory and practice are the same. In practice, they are not.
March 12, 2018 at 3:17 am
Yes, I became so interested in the family names that were valid SQL that the editorial was already a bit lengthy, so I didn't mention the obvious panacea of parameterizing the query. Sorry about that!
Best wishes,
Phil Factor
March 12, 2018 at 6:31 am
parameterizing the query
Would that alone do it? Because if so, I have nothing to be concerned about. There are also other indirect safeguards in my design. Primarily is that I never use the dbo schema anymore, though it still exists in older designs. This prevents simply using a table name. The tables also have FK constraints. These would prohibit them simply being truncated per your example, although I know there are other possible bad requests.
March 12, 2018 at 8:10 am
You're correct, Users should never be able to access the base tables directly at all, and all calls to the database must be parameterised. Basically, although I believe that the presentation layer should do its own checking as well, the safest course for the database designer and developer is to assume that the user has console access to SQL Server. In a badly-written application things are almost like that. If the users get any freedom to execute SQL Statements, there is a danger that they can escalate their permissions if everything isn't nailed do. As far as database defense against SQL Injection goes, I reckon that the article I wrote Schema-Based Access Control for SQL Server Databases is relevant. Even with schema-based security, though, there are still risks of SQL Injection, especially if SQL Server is badly configured.(See Windows privilege escalation script PowerUp by Will Schroeder).
Best wishes,
Phil Factor
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply