Lets run Arbitrary SQL code!
-
@Mikael_Svahnberg said in Lets run Arbitrary SQL code!:
get the database to do something else when they do enter these commands?
That would be pretty nifty, but there are situations when deleting from the table is a valid action (just not from this user) and indeed, would be better handled the easy way by just using a read-only account.
-
@fwd sqlparse exists for Python.
-
@Tsaukpaetra check who the user is before performing the delete?
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
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.
Run away.
-
@Tsaukpaetra You're not mentioning your DB engine. If you're on oracle (doesn't sound like it) you can use the statement attribute OCI_ATTR_SQLFNCODE after preparing to determine the type of query. Other engines may allow something similar.
-
@Vaire said in Lets run Arbitrary SQL code!:
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."
Because they know 6 months after that it will be revived as mission-critical-oh-god-we're-going-to-die-if-it's-not-released-tomorrow?
-
@dcon said in Lets run Arbitrary SQL code!:
@Vaire said in Lets run Arbitrary SQL code!:
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."
Because they know 6 months after that it will be revived as mission-critical-oh-god-we're-going-to-die-if-it's-not-released-tomorrow?
What is it with people lately bringing their "logic" and "reason" into my perfectly happy online rantings? o_O
-
It depends on what you mean by "arbitrary". Do you have a table with a list of allowed queries, or is it literally a textarea that you paste the values you want?
Given the square brackets, I'm assuming you're running on SQL Server. Given that and assuming you're not using the textarea approach, Is there some reason you're not using
sp_executesql
? You can use that to execute parameterized SQL statements.Here's an example from the MS doc:
DECLARE @IntVariable int; DECLARE @SQLString nvarchar(500); DECLARE @ParmDefinition nvarchar(500); /* Build the SQL string one time.*/ SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID FROM AdventureWorks2012.HumanResources.Employee WHERE BusinessEntityID = @BusinessEntityID'; SET @ParmDefinition = N'@BusinessEntityID tinyint'; /* Execute the string with the first parameter value. */ SET @IntVariable = 197; EXECUTE sp_executesql @SQLString, @ParmDefinition, @BusinessEntityID = @IntVariable; /* Execute the same string with the second parameter value. */ SET @IntVariable = 109; EXECUTE sp_executesql @SQLString, @ParmDefinition, @BusinessEntityID = @IntVariable;
If your table contains the
@SQLString
and the@ParamDefinition
, then you should at least be able to prevent truly arbitrary code execution.If, on the other hand, you've just got the text area with a prompt that says, "Paste your SQL query here," then you're fucked.
-
Could you maybe create a transaction, execute the SQL, and then roll back the transaction, to ensure that nothing in it could change the underlying data?
Even if you could, you'd still need to make sure the SQL didn't try to commit the transaction, I guess.
-
@PleegWat said in Lets run Arbitrary SQL code!:
You're not mentioning your DB engine
Oh sorry, thought I had.
This is MS SQL Server (2008 R2-ish?)
The target application is an ASP.Net application (.Net 4.5 I think?) hosted internally.
The site's permissions on the relevant database:
@BaconBits said in Lets run Arbitrary SQL code!:
It depends on what you mean by "arbitrary". Do you have a table with a list of allowed queries, or is it literally a textarea that you paste the values you want?
...
If, on the other hand, you've just got the text area with a prompt that says, "Paste your SQL query here," then you're fucked.The current draft of the userstory is a flat textarea on the page. :(
@anotherusername said in Lets run Arbitrary SQL code!:
the transaction,
Can you even have transactions inside a subquery?
- scribbles some notes of things to try on the "hacking ideas" notepad
-
I hope you'll be keeping us updated when this goes massively wrong some how? ;)
-
@loopback0 said in Lets run Arbitrary SQL code!:
I hope you'll be keeping us updated when this goes massively wrong some how?
Sure!
Though I might need to rope in help, I don't anticipate this screen being used any more than the User Administration screen I built (or, for that matter, the Lookup Administration screen...).
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
I don't anticipate this screen being used any more than the User Administration screen I built
Ha.
We've all seen enough stories of something that's implemented as a dirty way of doing one little thing and ends up all over the place.
-
@cartman82 said in Lets run Arbitrary SQL code!:
Your solution with SELECT wrapper seems feasible, but long term, feels like something that'll bite you in the ass somehow. Just a hunch.
It's easy to break out of. And once the shitty developers learn the "secret" of breaking out of it, they'll just shove that in every query out of habit. Then your protection is gone entirely. (It's like the sudo thing in Linux; once users learn that this magic word "sudo" causes mysterious errors to disappear, they'll just use it all the time and the protection it provides is out the window.) Or you have to write a new text transform to make that method of breaking out not work and it's an arms race.
The only solution to your problem that isn't a WTF is to fix your change management process. It's not something you can do in an SSIS window.
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
@BaconBits said in Lets run Arbitrary SQL code!:
It depends on what you mean by "arbitrary". Do you have a table with a list of allowed queries, or is it literally a textarea that you paste the values you want?
...
If, on the other hand, you've just got the text area with a prompt that says, "Paste your SQL query here," then you're fucked.The current draft of the userstory is a flat textarea on the page. :(
Yeah, about the best you can do is log everything that page is asked to do with the exact query entered, the date and time, the IP address of the user, and the username used to authenticate the session. The date and time will be useful for point-in-time recovery when someone forgets the WHERE clause in an UPDATE statement.
I'd consider anything except the most naive and ham-handed parsing (i.e., no instances of the words UPDATE, INSERT, DELETE, EXEC or MERGE) to be a fool's errand because SQL syntax is absurdly flexible. (Just look at the StackOverflow questions where people say they have queries that work but return unexpected results if you want examples!) All you're doing by trying to parse it while not being the actual server's query engine is building a puzzle.
If you can't protect the system from damage, make sure you're able to assign the blame correctly!
-
@Vaire said in Lets run Arbitrary SQL code!:
@dcon said in Lets run Arbitrary SQL code!:
@Vaire said in Lets run Arbitrary SQL code!:
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."
Because they know 6 months after that it will be revived as mission-critical-oh-god-we're-going-to-die-if-it's-not-released-tomorrow?
What is it with people lately bringing their "logic" and "reason" into my perfectly happy online rantings? o_O
I'm giving you plenty of lead time to wind up a new rant for when this happens.
-
@blakeyrat said in Lets run Arbitrary SQL code!:
It's easy to break out of. And once the shitty developers learn the "secret" of breaking out of it, they'll just shove that in every query out of habit.
Well, yes. But this screen won't be getting that kind of mileage anyways so I'm banking on:
- The few users that use it won't have time needed to devote to intentionally breaking it
- Those that would want to break it intentionally don't exist (for all intents and purposes).
- If the people that want to break it actually do exist, they wouldn't try it through this screen (because they'd likely already have the actual user's password anyways; it's right there in the config files in Source Control, "encrypted")
@blakeyrat said in Lets run Arbitrary SQL code!:
The only solution to your problem that isn't a WTF is to fix your change management process.
I love how this apparently seems to be a do-able thing in your mind. This change management process has existed since 2002 (If I can find the archive file shares I might even be able to screenshot the Windows 95 screens used to describe how to fill out our Word documents about this). This process has existed and remained relatively unchanged for over a decade, and little ol' me won't be the nail to get it to change.
We're so into the "don't change the process" mentality that the people in charge approved a Lotus Notes To Web conversion thing that provides a view into the Service Request nsf database (Make sure IE is in Compatibility mode though!).
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
I love how this apparently seems to be a do-able thing in your mind
Maybe you can't change it - but you can also not implement this solution.
I assume no-one's holding a gun to your head making you do it. You've already admitted it's a "side project" which isn't "approved".
-
@loopback0 I got the impression that the "allow the user to execute arbitrary SQL" thing was already written and in place, and the "side project" is actually just fixing it.
-
@anotherusername said in Lets run Arbitrary SQL code!:
was already written and in place
It was referred to as a draft of a user story.
Irregardless - even in the WTF-ridden company I work for, I'd be expected to either report it to the grown ups or disable it, and it'd be unavailable until it was reimplemented properly.
-
@loopback0 the "grown ups" are more likely to think that "working" is what it is already, with the giant gaping security hole, and "broken" is what it will be if you rip out the bad code and tell the users "sorry, no dice".
-
@anotherusername said in Lets run Arbitrary SQL code!:
@loopback0 I got the impression that the "allow the user to execute arbitrary SQL" thing was already written and in place, and the "side project" is actually just fixing it.
Something like that. The process that executes the arbitrary SQL is in place and running splendidly, just the process to manage these queries is very manual and prone to fleshy error.
@loopback0 said in Lets run Arbitrary SQL code!:
report it to the grown ups or disable it, and it'd be unavailable until it was reimplemented properly.
For an editor that doesn't exist yet, it's quite easy to disable.
That's what this discussion is about: to see if there is a sufficiently safe way of allowing arbitrary queries to run.
It is assumed that most queries that would be input would be benign, but we're looking for some level of "oops proofing".
-
@anotherusername said in Lets run Arbitrary SQL code!:
the "grown ups" are more likely to think that "working" is what it is already, with the giant gaping security hole, and "broken" is what it will be if you rip out the bad code and tell the users "sorry, no dice".
Not where I work - at least not when it's the IT grown ups. All that's required is pointing out that something is either a security issue, or that it going wrong could cause "brand damage" and everyone who matters in IT listens.
We're a UK ISP and this stuff was adhered to before the TalkTalk incident - it's even more effective now.
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
to see if there is a sufficiently safe way of allowing arbitrary queries to run.
Yes - under a sufficiently restricted user.
-
I'm actually really curious if someone had a realistic idea of whether my transactions idea could work.
I mean, the database probably already has the ability to guarantee that some atomic transaction can be rolled back however necessary to avoid changing the underlying data, so it seems like you could use that to your advantage somehow.
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
This process has existed and remained relatively unchanged for over a decade, and little ol' me won't be the nail to get it to change.
So just follow it?
-
@anotherusername said in Lets run Arbitrary SQL code!:
I'm actually really curious if someone had a realistic idea of whether my transactions idea could work.
It could, but by the time you've bothered implementing it, you might as well just do it properly.
-
@loopback0 well, "do it properly" sounds like a no-go on account of not being able to create a readonly user.
I agree that it would be the correct way to do it, but it sounds like he's up against some painful red tape.
-
@loopback0 said in Lets run Arbitrary SQL code!:
@anotherusername said in Lets run Arbitrary SQL code!:
the "grown ups" are more likely to think that "working" is what it is already, with the giant gaping security hole, and "broken" is what it will be if you rip out the bad code and tell the users "sorry, no dice".
Not where I work - at least not when it's the IT grown ups. All that's required is pointing out that something is either a security issue, or that it going wrong could cause "brand damage" and everyone who matters in IT listens.
We're a UK ISP and this stuff was adhered to before the TalkTalk incident - it's even more effective now.Haaaaahahahaha.
If only companies worked that way here..........
-
@loopback0 said in Lets run Arbitrary SQL code!:
Yes - under a sufficiently restricted user.
Yes, we already said this was the best idea.
@boomzilla said in Lets run Arbitrary SQL code!:
So just follow it?
Well I have to. Duh. But I'm not a fan of the paperwork and multi-colored tape required to get another user. To get the one I have took six months, and that was prioritized (because the prior iteration was using a much more powerful user somehow and that was even worse)!
@anotherusername said in Lets run Arbitrary SQL code!:
some painful red tape.
This. You heard of red tape? Well I have Green, yellow, and cyan tape to deal with....
-
@sloosecannon said in Lets run Arbitrary SQL code!:
If only companies worked that way here..........
It's just about playing the stupid corporate politics. None of the executives want to be responsible for a major corporate fuck up, so they always pass the blame to the IT directors. The IT directors don't want it either, so when those of us with the right level of experience tell them it's a bad idea, they listen.
It's pretty much the only times I play the corporate politics game, rather than ignoring it.
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
Well I have Green, yellow, and cyan tape to deal with....
All in red ink, presumably.
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
I love how this apparently seems to be a do-able thing in your mind.
Nothing worthwhile is easy.
-
@blakeyrat said in Lets run Arbitrary SQL code!:
Nothing worthwhile is easy.
Eating and drinking are (usually) fairly low on the difficulty scale.
-
@Tsaukpaetra That's why I have to inflate the difficulty. For lunch I'm having piping-hot soup while riding a unicycle on I-5.
-
@blakeyrat said in Lets run Arbitrary SQL code!:
For lunch I'm having piping-hot soup while riding a unicycle on I-5.
Ah, but is doing so worthwhile? See, things that are worthwhile doing and things that are difficult aren't necessarily correlated.
Besides, your soup should cool off pretty well to a good temperature by the time you're stable, so then it becomes a matter of avoiding cars and the highway patrol.
-
@Tsaukpaetra I can actually ride the unicycle on more or less level ground without falling off or killing myself, and still I wouldn't dream of doing it with hot soup or anywhere near traffic.
-
@anotherusername said in Lets run Arbitrary SQL code!:
still I wouldn't dream of doing it with hot soup or anywhere near traffic.
So you've covered the "it's difficult" part, but still haven't qualified the "it's worthwhile" part.
-
@Tsaukpaetra it isn't.
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
But I'm not a fan of the paperwork and multi-colored tape required to get another user. To get the one I have took six months, and that was prioritized (because the prior iteration was using a much more powerful user somehow and that was even worse)!
So you'll have at least six months to not worry about this problem. Sounds like a winner to me.
-
@anotherusername said in Lets run Arbitrary SQL code!:
it isn't
Give this dog a bone, it seems he might have gotten my point!
-
@boomzilla said in Lets run Arbitrary SQL code!:
So you'll have at least six months to not worry about this problem. Sounds like a winner to me.
Yep. Like I said, side project, not a priority. But if I do decide to start on that path, I'll want to have a good reason why it's going to take that long.
Actually, I can simple not tell the users that I'm working on this and just magic it into the system when it all goes through in half a year. Then I'd look like a saint, right?
Filed under: I hate multiquoting on Mobile
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
@boomzilla said in Lets run Arbitrary SQL code!:
So you'll have at least six months to not worry about this problem. Sounds like a winner to me.
Yep. Like I said, side project, not a priority. But if I do decide to start on that path, I'll want to have a good reason why it's going to take that long.
Actually, I can simple not tell the users that I'm working on this and just magic it into the system when it all goes through in half a year. Then I'd look like a saint, right?
Filed under: I hate multiquoting on Mobile
You may want to run a full system scan for sleaziness and beauracracy infections. You're showing some warning signs
-
select * from one_billion_row_table a, one_hundred_billion_row_table b where 'a.id' = b.id
oops
-
@Tsaukpaetra said in Lets run Arbitrary SQL code!:
I'll want to have a good reason why it's going to take that long
Doing it properly.
-
@darkmatter Oh, yeah, and delete the system account of any user who doesn't use ANSI joins.
-
@sloosecannon said in Lets run Arbitrary SQL code!:
You may want to run a full system scan for sleaziness and beauracracy infections. You're showing some warning signs
Oh dear! That sounds pretty dangerous indeed! Should I open eventvwr to see all the infections? ;)
@loopback0 said in Lets run Arbitrary SQL code!:
Doing it properly.
Somehow that only seems to go so far...
In any case, nothing to worry about for a blackbook project anyways, right?@boomzilla said in Lets run Arbitrary SQL code!:
use ANSI joins.
TBH I forgot not doing this was actually a thing...
-
@boomzilla said in Lets run Arbitrary SQL code!:
who doesn't use ANSI joins.
The particular patch version of Oracle we use has a bizarre bug where if the sum of the columns in all tables involved in ANSI joins is above X (I forget what X is, but it's in the hundreds) then it throws an unrelated error. While upgrading to a newer patch level is planned, the workaround in the mean time is to either replace all ANSI joins with the Oracle syntax or replace some ANSI joins with Oracle joins.
The result of the latter is
-
@boomzilla said in Lets run Arbitrary SQL code!:
delete the system account of any user who doesn't use ANSI joins
select * from one_billion_row_table a join one_hundred_billion_row_table b on a.id = b.id or a.really_small_field < b.really_big_field
though I'm with you on banning people that use ORs in key joins
-
which reminds me of some other random shit I actually saw and wept about once... a query very similar to:
select * from one_billion_row_table a join one_hundred_billion_row_table b on coalesce(a.id,b.id) = coalesce(b.id,a.id)