SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Troublesome Names


Troublesome Names

Author
Message
Phil Factor
Phil Factor
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15950 Visits: 3135
Comments posted to this topic are about the item Troublesome Names


Best wishes,

Phil Factor
Simple Talk
richard-674310
richard-674310
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 100
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.


RonKyle
RonKyle
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25975 Visits: 4343
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.




tom-864693
tom-864693
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 51
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/
Phil Factor
Phil Factor
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15950 Visits: 3135
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
Simple Talk
RonKyle
RonKyle
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25975 Visits: 4343
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.




Phil Factor
Phil Factor
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15950 Visits: 3135
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
Simple Talk
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search