September 25, 2017 at 7:38 am
I write and leave on my servers a fair number of queries that use dynamic SQL. Users of the primary databases that I deal with regularly have new ideas, reporting requirements or general questions about something in the data. Various users have various editing privileges, for which I have set up roles. However, I do not always have time to do a good job of sanitizing everything, so I have created a schema with read-only privileges on all tables, since any user may read anything (in his database).
When I need to slap together a query or stored procedure that uses dynamic SQL, and I don't have the leisure to write properly parameterized and sanitized SQL, I always put it into the RO schema. Action procedures I test and configure properly, no matter how long it takes, but I have been dumping more and more stuff into my RO schema – combobox and listbox source queries, statistics queries – anything that only reads. Is this enough to prevent injection attacks via these procedures?
Seems to me it should be enough, since the entire schema has no privilege besides SELECT on all tables, so no matter what someone sneaks into a parameter, it should crash if it tries to do anything but read. However, I have been wrong on many 'obvious' things about SQL Server, so if I'm wrong in my understanding of this, I'd rather find out here, than on my servers.
September 25, 2017 at 8:31 am
Hmm... there aren't read only schemas, do you mean read only permissions for a login/role?
But more importantly why are you not worried about SQL injection? Do any of the procedures use EXECUTE AS?
September 25, 2017 at 8:38 am
SQL injection generally occurs because parameters are capable of holding a lot of data. So if you have a character-based parameter that is longer than say 15 to 20 characters, and an application which allows free-form typing into that data field, and the query simply appends that string to the rest of the query without any validation, that's a hole a mile-wide that needs to get fixed. Nothing bad about having the RO schema, and certainly a good idea, but validating parameters is the most valuable thing you can do, and best to do so within the application.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 25, 2017 at 8:55 am
ZZartin - Monday, September 25, 2017 8:31 AMHmm... there aren't read only schemas, do you mean read only permissions for a login/role?But more importantly why are you not worried about SQL injection? Do any of the procedures use EXECUTE AS?
Yeah, sorry - that was sloppily worded. I meant a schema containing a single database role, and that role has read-only privileges on tables, and execute privileges only on procedures that ARE properly sanitized.
And why do you write that I'm not worried about injection? I am, somewhat, although this is a fairly safe environment - that's why I posted this question.
September 25, 2017 at 9:04 am
sgmunson - Monday, September 25, 2017 8:38 AMSQL injection generally occurs because parameters are capable of holding a lot of data. So if you have a character-based parameter that is longer than say 15 to 20 characters, and an application which allows free-form typing into that data field, and the query simply appends that string to the rest of the query without any validation, that's a hole a mile-wide that needs to get fixed. Nothing bad about having the RO schema, and certainly a good idea, but validating parameters is the most valuable thing you can do, and best to do so within the application.
Some of my parameters are fairly large, and difficult to sanitize, since they search for data that is not particularly well structured. And after years of working on this, I'm still a little vague on the interaction between roles, logins, users, schemas, inherited permissions and all of that. I've studied endless blog posts, forum posts, e-books, experimented with all sorts of things, but I still feel like I'm missing a solid overall grasp of the subject. Fortunately, this is a fairly low-threat environment, with no data that is valuable to anyone on the outside, and I'm religious about backups, so I'm not too worried, but I'd like to understand it all better.
September 25, 2017 at 9:24 am
pdanes - Monday, September 25, 2017 9:04 AMsgmunson - Monday, September 25, 2017 8:38 AMSQL injection generally occurs because parameters are capable of holding a lot of data. So if you have a character-based parameter that is longer than say 15 to 20 characters, and an application which allows free-form typing into that data field, and the query simply appends that string to the rest of the query without any validation, that's a hole a mile-wide that needs to get fixed. Nothing bad about having the RO schema, and certainly a good idea, but validating parameters is the most valuable thing you can do, and best to do so within the application.Some of my parameters are fairly large, and difficult to sanitize, since they search for data that is not particularly well structured. And after years of working on this, I'm still a little vague on the interaction between roles, logins, users, schemas, inherited permissions and all of that. I've studied endless blog posts, forum posts, e-books, experimented with all sorts of things, but I still feel like I'm missing a solid overall grasp of the subject. Fortunately, this is a fairly low-threat environment, with no data that is valuable to anyone on the outside, and I'm religious about backups, so I'm not too worried, but I'd like to understand it all better.
One of the ways to sanitize long parameters involves some SQL keyword checking, and looking for obvious problems like the mere presence of the words TRUNCATE or DELETE or ROLLBACK or COMMIT or GRANT or ALTER. You can even go further and use a good string splitter and then count keyword matches with a table of SQL keywords. Jeff Moden's DelimitedSplit8K is particularly well performing for such a task, although going through that much effort for every record could still be problematic if the volume of things gets high enough.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply