Troublesome Names

  • Comments posted to this topic are about the item Troublesome Names

    Best wishes,
    Phil Factor

  • 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.

  • 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. 

  • 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.

  • 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

  • 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.

  • 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