Lets run Arbitrary SQL code!


  • Notification Spam Recipient

    So I have a process that will load up an arbitrary SQL statement and execute it, spitting the result into a file that the user specifies. This works fine and dandy, except that (for obvious reasons) it's horribly insecure as all get out.

    A quick Google around indicates many solutions that revolve around just sending the statement to the database server and telling it to "prepare only", getting back if there was an error or not, however I want to go a bit further and weed out instances of things like DROP and UPDATE etc.

    Simply searching for key terms won't be enough, as there are real cases where these are column names (encapsulated in brackets), for example [Performance Drop] and [UPDATED DATE].

    Is there an easy way to trap undesired keywords (so I can prevent saving them) somehow?

    The user this will be running under can't do most DDL anyways, but it can DELETE and UPDATE among a few other dangerous write-permission things due to it being a service user meant to move data around the databases (and unfortunately no, there isn't any current plans to add a new read-only user, as easy as it would be, due to bureaucracy).

    Hints or suggestions?

    I was thinking if I can' get free-form text to easily validate I would just have to craft up some GUI drag-and-drop interface or something.


  • ♿ (Parody)

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    and unfortunately no, there isn't any current plans to add a new read-only user, as easy as it would be, due to bureaucracy

    I think this was really your only hope. What sorts of users are you planning to open this up to?


  • Discourse touched me in a no-no place

    @boomzilla said in Lets run Arbitrary SQL code!:

    I think this was really your only hope

    Yup.



  • @Tsaukpaetra They don't need write access to be able to fuck your database over; if they were motivated, they could easily compose a query that (when executed) would effectively serve as a denial-of-service to everybody else.


  • Notification Spam Recipient

    @boomzilla said in Lets run Arbitrary SQL code!:

    What sorts of users are you planning to open this up to?

    Idiots Well, ideally people smart enough not to try. These will be the so-called admins on the site, so in theory I shouldn't have to worry. And it's an Internal site so supposedly only those smart enough and with the right access should be able to get to this screen anyway, so... Blurb.

    @blakeyrat said in Lets run Arbitrary SQL code!:

    They don't need write access to be able to fuck your database over; if they were motivated, they could easily compose a query that (when executed) would effectively serve as a denial-of-service to everybody else.

    I would hope our users aren't that kind of malicious, and I doubt they would have that level of knowledge or desire to do this anyways.

    I'm more on the "Oops I didn't realize we couldn't delete things from the reports like that" area of mistakes than "ZOMG We need to protects ourselves from all the haxors!"



  • @Tsaukpaetra I worked for a company once that had implemented a "report builder" feature that could DoS the database on accident. It happened more than once.

    Anyway not really relevant to your question (to which I'd probably answer: you're just trying to make a bad solution better by adding more bad layers to it), so I'll clam up.



  • probably need a middle layer with a full sql parser that can break a query into individual statements, which you can then filter by type. I have no idea if such a thing exists.



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    So I have a process that will load up an arbitrary SQL statement and execute it, spitting the result into a file that the user specifies. This works fine and dandy, except that (for obvious reasons) it's horribly insecure as all get out.

    A quick Google around indicates many solutions that revolve around just sending the statement to the database server and telling it to "prepare only", getting back if there was an error or not, however I want to go a bit further and weed out instances of things like DROP and UPDATE etc.

    Simply searching for key terms won't be enough, as there are real cases where these are column names (encapsulated in brackets), for example [Performance Drop] and [UPDATED DATE].

    Is there an easy way to trap undesired keywords (so I can prevent saving them) somehow?

    The user this will be running under can't do most DDL anyways, but it can DELETE and UPDATE among a few other dangerous write-permission things due to it being a service user meant to move data around the databases (and unfortunately no, there isn't any current plans to add a new read-only user, as easy as it would be, due to bureaucracy).

    Hints or suggestions?

    I was thinking if I can' get free-form text to easily validate I would just have to craft up some GUI drag-and-drop interface or something.

    My god ... everything you just said ... is terrifying =_=


  • ♿ (Parody)

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    I would hope our users aren't that kind of malicious, and I doubt they would have that level of knowledge or desire to do this anyways.

    Anyone, expert or novice, can miss something in a query that chews up all your CPU or temp space or I/O.


  • Notification Spam Recipient

    @blakeyrat said in Lets run Arbitrary SQL code!:

    a "report builder" feature that could DoS the database on accident.

    Oh yes, it's easy to do this here as well, as whatever query is used gets executed about three times, each one inside their own transaction. I'll admit I'm my own :wtf:, but this seemed to be the best solution instead of requiring a three-week minimum red-tape round-trip with production control to edit an SSIS package for every little tiny adjustment to these exports.

    @blakeyrat said in Lets run Arbitrary SQL code!:

    you're just trying to make a bad solution better by adding more bad layers to it

    Unfortunately.

    @fwd said in Lets run Arbitrary SQL code!:

    probably need a middle layer with a full sql parser that can break a query into individual statements, which you can then filter by type

    Ideally it should be only a single Select statement actually...

    @Vaire said in Lets run Arbitrary SQL code!:

    My god ... everything you just said ... is terrifying =_=

    I do some pretty terrifyingble stuff sometimes. Sometimes my life is awesomeful.

    @boomzilla said in Lets run Arbitrary SQL code!:

    Anyone, expert or novice, can miss something in a query that chews up all your CPU or temp space or I/O.

    Yes. It's especially fun when two out of three environments (the third being Production) say it's supposed to take a few milliseconds to execute a query (with identical tables/indexes/partitions/etc. mind you!) and the third saying, "Nope, I'm going to sit here like an ass chomping on a few INSERT statements that are putting in one record each!"

    Curious addendum to the question: If I wrap the input as a subquery, things like UPDATE, DROP, and DELETE are invalid, right?

    So I could in theory turn:

    Select MyBlob, OfColumns
    From WhateverTable
    Where Things='Happen'
    

    into:

    Select top 1 1 Check
    FROM (
    Select MyBlob, OfColumns
    From WhateverTable
    Where Things='Happen'
    ) TheAlias
    

    And that would (in theory) rat out invalid stuff?
    I know it definitely kicks out Order By clauses, but I don't remember if it did other things too...



  • @boomzilla I am telling you right now, if someone in management is making you do this, get it in writing for your objections, and get in writing them making you do it despite your objections. This is the kind of thing that someone WILL eventually be looking for someone to blame for. I guarantee it.


  • Discourse touched me in a no-no place

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    And that would (in theory) rat out invalid stuff?

    Yes but...

    Ewwwwwww.

    :runaway:



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    (and unfortunately no, there isn't any current plans to add a new read-only user, as easy as it would be, due to bureaucracy).

    A read-only user is THE proper solution.

    If you still end up having to do something else, count the hours it took, multiply by your salary and be sure to let someone higher up know that their bureaucracy cost the company $x.

    There are only two other solutions I can think of:

    1. Generating the code yourself from whitelisted "templates" somehow.
    2. Parsing the SQL statements properly (find the formal syntax somewhere), then accepting commands and parameters from a white list (i.e. same thing you'd do to sanitize HTML).

  • Notification Spam Recipient

    @loopback0 said in Lets run Arbitrary SQL code!:

    Yes

    Hey, for now, that's all that's required.

    The TOP clause is not allowed when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
    

    So it should weed out most "oops" situations, and if you're intelligent enough to determine that you need to break out of the statement you're doing stuff anyways and I'm not going to attempt to code around that.

    I mean, merely fooling around and getting back probably two specific error messages should be enough to clue an evildoer what needs to be done anyways.

    @anonymous234 said in Lets run Arbitrary SQL code!:

    A read-only user is THE proper solution.

    Agreed, that's not under debate.

    @anonymous234 said in Lets run Arbitrary SQL code!:

    count the hours it took,

    About five minutes to have the app surround the input with some extra text. And I am not making nearly enough to care enough to argue this with them. It was hard enough getting them to allow the creds I do have to Execute stored procedures in Prod, they'd probably much something up and remove permissions from this main one while trying to add in the secondary one (it's happened at least three times on unrelated occasions while they were "cleaning up" users).

    @anonymous234 said in Lets run Arbitrary SQL code!:

    Generating the code yourself from whitelisted "templates" somehow.

    This might be doable, but then I'd basically be re-inventing MS Query Builder...

    @anonymous234 said in Lets run Arbitrary SQL code!:

    Parsing the SQL statements properly (find the formal syntax somewhere), then accepting commands and parameters from a white list (i.e. same thing you'd do to sanitize HTML).

    If the current idea of surrounding their input in a Subquery doesn't pan out, I might just have to do this.

    Either that or keep close watch on who's allowed to edit these things (in theory it would be just me, and possibly some other BA I would train up or something).



  • If you're dealing with a determined hacker, read only is the only solution.

    But since it sounds like you're more like defending against clumsy idiots, you don't really need need to make it 100% bullet proof. Don't let perfect be the enemy of good enough. There's plenty you can do with just a few dumb obvious regexes.

    Your solution with SELECT wrapper seems feasible, but long term, feels like something that'll bite you in the ass somehow. Just a hunch.


  • Notification Spam Recipient

    @cartman82 said in Lets run Arbitrary SQL code!:

    that'll bite you in the ass somehow. Just a hunch.

    I plan to be gone by the time that happens ⏲

    Trust me, this is the company that's only now trying to (somewhat seriously) get rid of Access 97. By the time they realize this might be a potential hole I'll have retired...



  • @Tsaukpaetra I'm sure people who didn't want to give you read only access thought the same way...


  • Discourse touched me in a no-no place

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    The user this will be running under can't do most DDL anyways, but it can DELETE and UPDATE among a few other dangerous write-permission things due to it being a service user meant to move data around the databases (and unfortunately no, there isn't any current plans to add a new read-only user, as easy as it would be, due to bureaucracy).

    So, the threat model is “protect against dumbasses”? Can you take simple steps to ensure that this is indeed true? This might well include making sure that the DB is on a network address that won't route to the outside world, and also making sure that nobody can use any of the interfaces to this without logging in properly (check that that is true after deployment!)

    Unless you've got a threat model, you won't make sane security decisions.


  • Notification Spam Recipient

    @dkf said in Lets run Arbitrary SQL code!:

    This might well include making sure that the DB is on a network address that won't route to the outside world

    Pretty sure this is true, I don't manage that part of the db server/network infrastructure. For all intents and purposes, we're assuming a supposed attack would only be coming from an internal user granted permissions on this application, which happens to have Read/Write/Execute to the target database.

    @dkf said in Lets run Arbitrary SQL code!:

    make sane security decisions.

    The thread title should have clued you in that I know this is a Bad Idea, but I don't want to be the single point-of-failure for when I leave and someone has to update these Export queries (I'm not that mean).

    It's not all that sane, but I'd rather build out a somewhat locked-down interface to update these records than rely on them (correctly) crafting the required Insert or Update statement and hope that Production Control sufficiently vets anything wacky before smashing it into the server.



  • @dkf By definition, though, ANY connectivity to the corporate network is connectivity to the outside world. Because somewhere on the corporate network is a computer that is piloted by an end user. And at some point, that end user WILL click here to learn how to get a free vacation in ${TROPICAL_PARADISE} (or will just browse to a plain old website while that website has a bad ad up in the rotation), and then boom, your SQL server is exposed to the Internet.


  • Discourse touched me in a no-no place

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    For all intents and purposes, we're assuming a supposed attack would only be coming from an internal user granted permissions on this application, which happens to have Read/Write/Execute to the target database.

    Well, you'll never figure out a good way to distinguish a permitted action from a prohibited one.

    Except maybe checking for if the network packets have the EVIL bit set.


  • Discourse touched me in a no-no place

    @izzion It doesn't make sense to worry about that too much unless the data is really sensitive; DBAs visit websites too. We know that can't be the case here though, since @Tsaukpaetra is implementing something that allows arbitrary access to the data…


  • Notification Spam Recipient

    @dkf said in Lets run Arbitrary SQL code!:

    Well, you'll never figure out a good way to distinguish a permitted action from a prohibited one.

    Unless the app that's driving the database handles prohibited actions itself (hence this thread).

    @dkf said in Lets run Arbitrary SQL code!:

    something that allows arbitrary access to the data…

    Right, and since we can't rightly use a neutered user to do our vetting for us from the DB backend, I'm trying to find a just-as-good equivalent to paper-sign away the frontend.

    In practice the target demographic already has read-only access to the database themselves (but they probably don't know it) but I'm finding it even harder to let pass-through authentication work for this than just keeping with the generic website's credentials.


  • Discourse touched me in a no-no place

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    I'm finding it even harder to let pass-through authentication work for this than just keeping with the generic website's credentials.

    You don't need pass-through authentication. You can just have the arbitrary queries run as a restricted user, and the rest of the app can remain as is.


  • ♿ (Parody)

    @Vaire said in Lets run Arbitrary SQL code!:

    @boomzilla I am telling you right now, if someone in management is making you do this, get it in writing for your objections, and get in writing them making you do it despite your objections. This is the kind of thing that someone WILL eventually be looking for someone to blame for. I guarantee it.

    If @Tsaukpaetra can stick me with the blame he deserves to get away with it.


  • ♿ (Parody)

    Can you run a query plan? You could refuse to execute anything exceeding some cost. It's not foolproof, because those things can be misleading, but it might save you some grief.



  • @Tsaukpaetra if you really want to do this, write an actual SQL parser for your DB (for the whole grammar, with parser combinators) and parse the strings and reject anything except select statements.

    Regular expressions are not good enough. Luckily, SQL grammars are easy to parse for real.



  • My only question is there a real 'crunch' for the deadline? If not just wait and get the user created, if so just get it in writing like @Captain and others have stated and move on. Make em wait.



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    A quick Google around indicates many solutions that revolve around just sending the statement to the database server and telling it to "prepare only", getting back if there was an error or not, however I want to go a bit further and weed out instances of things like DROP and UPDATE etc.

    The end result of this is an arms race that, no matter how hard you try, will end in a world of hurt. For example:

    declare @v nvarchar(max)
    
    set @v = 'u' + 'pdate WhateverTable set...'
    
    exec(@v)
    

    Or...

    declare @v nvarchar(max)
    
    -- drop table WhateverTable
    set @v = 0x640072006F00700020007400610062006C0065002000570068006100740065007600650072005400610062006C006500
    
    exec(@v)
    

    Even if you ban DECLARE and EXECUTE and all their variants (which would greatly reduce the utility of your reporting solution), I might still be able to slip something past your filter. Friends don't let friends attempt to parse SQL.


  • Notification Spam Recipient

    @loopback0 said in Lets run Arbitrary SQL code!:

    You can just have the arbitrary queries run as a restricted user

    I need one first!

    @boomzilla said in Lets run Arbitrary SQL code!:

    Can you run a query plan?

    Not sure. Using the Entity Framework, so I'm sure it's possible, just haven't tried as such.

    @darth_llama said in Lets run Arbitrary SQL code!:

    My only question is there a real 'crunch' for the deadline? If not just wait and get the user created, if so just get it in writing like @Captain and others have stated and move on. Make em wait.

    If I'm going to be honest, this is technically a side project, which is why I can't really push to have a new user for this, because it's technically not yet "approved". In other words, the current plan of action is to have any modifications shoved in via request to Production Control, and hope and pray they got it right.

    @Groaner said in Lets run Arbitrary SQL code!:

    The end result of this is an arms race that, no matter how hard you try, will end in a world of hurt. For example:

    Well, using the subquery method, you can't Declare variables like so (and I believe you can't have multiple statements anyways), so that case is handled on accident? 🎊


  • Notification Spam Recipient

    @Groaner said in Lets run Arbitrary SQL code!:

    Even if you ban DECLARE and EXECUTE and all their variants (which would greatly reduce the utility of your reporting solution)

    To be fair, the main purpose of these Exports is to split out a big batch of "This is what happened since yesterday" into appropriate parcels as appropriate to their destination. In theory, none of these exports should be much more than a

    Select MyCrap, Columns 
    From BigOleTable 
    Where SomeSpecificColumnForThisExport = 'DefiningValue'
    

    If it's much more complicated than that, it probably shouldn't be done in that process anyways...


  • Trolleybus Mechanic

    @Tsaukpaetra How "arbitrary" is "arbitrary"?

    If the requirements specify that the user should be able to select some number of columns/functions and apply some kind of filtering, a possible solution would be to implement some manner of query builder that lets them pick what is queried and the selection criteria.

    Mind you, this would probably be more expensive, overall, than a read-only user.


  • Notification Spam Recipient

    @GOG said in Lets run Arbitrary SQL code!:

    possible solution would be to implement some manner of query builder that lets them pick what is queried and the selection criteria.

    Right, mentioned up thread.

    @anonymous234 said in Lets run Arbitrary SQL code!:

    Generating the code yourself from whitelisted "templates" somehow.

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    I was thinking if I can' get free-form text to easily validate I would just have to craft up some GUI drag-and-drop interface or something.

    But,

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    This might be doable, but then I'd basically be re-inventing MS Query Builder...



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    Well, using the subquery method, you can't Declare variables like so (and I believe you can't have multiple statements anyways), so that case is handled on accident? 🎊

    What if the user needs variables? Or a CTE? Or to aggregate data into a temp table?

    I started looking into some possible OPENQUERY abuse as my next attack vector , but then...

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    If it's much more complicated than that, it probably shouldn't be done in that process anyways...

    You're no fun :(.


  • Notification Spam Recipient

    @Groaner said in Lets run Arbitrary SQL code!:

    What if the user needs variables? Or a CTE? Or to aggregate data into a temp table?

    Yes. In theory, all the information they should ever need for this export is already contained in the BigOleTable, and if it's not you can still JOIN in some other table if needed

    @Groaner said in Lets run Arbitrary SQL code!:

    You're no fun .

    Well I am trying to mitigate my risk while implementing a feature what should make future work so much easier. Like I said, three weeks minimum to make a change to an export, regardless how small the change...



  • @Groaner Aha!

    select *
    from 
    (
    	select *
    	from openquery(servernamegoeshere, 
    	'declare @x nvarchar(max); 
    	set @x = ''drop table WhateverTable''; 
    	exec(@x) with result sets none
    	set @x = ''select 1 as result; commit transaction'';
    	exec(@x) with result sets ((result int))')
    ) t
    

    That addresses the subquery problem.


  • Notification Spam Recipient

    @Groaner said in Lets run Arbitrary SQL code!:

    select *

    The "safe checker" also has a Top 0 there, but I'm assuming it would still work in this case.

    Also, effort needed to know the server name yada etc. ;)



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    Well I am trying to mitigate my risk while implementing a feature what should make future work so much easier. Like I said, three weeks minimum to make a change to an export, regardless how small the change...

    You have the familiar reek of Enterprise-land on you. I bet if you named the company you are with, we have done business with them :D


  • Notification Spam Recipient

    @Vaire said in Lets run Arbitrary SQL code!:

    we have done business with them

    Got any loans? :trollface:



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    @Vaire said in Lets run Arbitrary SQL code!:

    we have done business with them

    Got any loans? :trollface:

    You don't want loaner code from us. The caves our code are kept in, are where dreams go to die.



  • @Groaner That's actually quite brilliant if your LS is setup correctly. The ones where I work don't allow you to even pass variables like that in an open-query. So similarly you'd have no problem if they were configured correctly. Query would simply fail at the inclusion of 'bad-actors'.



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    @Groaner said in Lets run Arbitrary SQL code!:

    select *
    The "safe checker" also has a Top 0 there, but I'm assuming it would still work in this case.

    Even with TOP 0, it still drops the table in my test environment. In all fairness, one does have to go out of one's way to enable OPENQUERY, though...

    Also, effort needed to know the server name yada etc. ;)

    select @@servername
    

    I presume that you're factoring into your risk assessment whether or not your users will be as evil as I am.


  • Notification Spam Recipient

    @darth_llama said in Lets run Arbitrary SQL code!:

    configured correctly

    Which, in my case, is probably on accident, not intentional... ;)


  • Notification Spam Recipient

    @Groaner said in Lets run Arbitrary SQL code!:

    your users will be as evil as I am.

    These are guys used to working in Access 97. I don't think they'll even know of all the cool tricks you can do, and if they have time to go around hacking a mostly-hidden web form, well....



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    @Groaner said in Lets run Arbitrary SQL code!:

    your users will be as evil as I am.

    These are guys used to working in Access 97. I don't think they'll even know of all the cool tricks you can do, and if they have time to go around hacking a mostly-hidden web form, well....

    I hope you document the fustercluck that this is going to be... for the archives ;)


  • Notification Spam Recipient

    @Vaire said in Lets run Arbitrary SQL code!:

    The caves our code are kept in

    Oh! You have a Cave of Wonders too? :D

    @Vaire said in Lets run Arbitrary SQL code!:

    I hope you document the fustercluck that this is going to be... for the archives

    Document? What's that? You're lucky if you get a JIRA ticket with a link to an SDLC document that (hopefully) contains sane information about what's going ont...



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    @Vaire said in Lets run Arbitrary SQL code!:

    The caves our code are kept in

    Oh! You have a Cave of Wonders too? :D

    It is more like a line that shall not be crossed. If that line is crossed ... bad things happen [makes blowing up motions with hands]



  • @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    @Vaire said in Lets run Arbitrary SQL code!:

    The caves our code are kept in

    Oh! You have a Cave of Wonders too? :D

    @Vaire said in Lets run Arbitrary SQL code!:

    I hope you document the fustercluck that this is going to be... for the archives

    Document? What's that? You're lucky if you get a JIRA ticket with a link to an SDLC document that (hopefully) contains sane information about what's going ont...

    I didn't mean real documentation, I meant here, where we can laugh at it :D
    Giving business people direct access to the database, even sanitized as you are trying to do, ALWAYS means comedy ;)



  • @Vaire said in Lets run Arbitrary SQL code!:

    @Tsaukpaetra said in Lets run Arbitrary SQL code!:

    @Vaire said in Lets run Arbitrary SQL code!:

    The caves our code are kept in

    Oh! You have a Cave of Wonders too? :D

    @Vaire said in Lets run Arbitrary SQL code!:

    I hope you document the fustercluck that this is going to be... for the archives

    Document? What's that? You're lucky if you get a JIRA ticket with a link to an SDLC document that (hopefully) contains sane information about what's going ont...

    I didn't mean real documentation, I meant here, where we can laugh at it :D
    Giving business people direct access to the database, even sanitized as you are trying to do, ALWAYS means comedy ;)

    Now, quit keeping me up, I need to go sleep so I can get up in the morning and work on code that I know damn well is never going to actually be put into production, because they are shutting that project down in 3 months. But they are insisting I code it anyway, for "completeness." [sigh]



  • Looking at the problem from the other side. Can we instead of preventing the user from entering "DROP" or "DELETE" or "RENAME" or whatever, get the database to do something else when they do enter these commands?

    Like a trigger or something? My SQL is rusty, but something like

    CREATE TRIGGER somename-or-other ON myTable
    INSTEAD OF DELETE AS RAISEERROR("SPANK SPANK! Bad User!")
    

Log in to reply