April 15, 2011 at 10:57 am
Revenant (4/15/2011)
Ninja's_RGR'us (4/12/2011)
I love MS & all since I'm earning my living because of them. BUT I work with ms Dynamics Nav and EVERY SINGLE FREAKING USER needs to be DBO.Granted this is the only db they have access to but this is a little moronic. And we're using the 2nd latest product version. So it looks like they still have some work to do there!
& BTW, there's a open / design table right there in the application... which anyone has access to. Our contact told us there's no real way to change that.
I reported this as a P0 to their test lead.
Thank you for bringing this up.
Thanks a million. Any way I can provide more feedback if / when I find bugs?
The other issue with this is that we have no real way to do implementations.
IE : I put db is single user mode to kick every one out. Backup, restore, checkdb. Put db in restricted user mode so our team can kick in (at which point I didn't know that every user now had access). Tell our consultant to start the upgrade process. In the middle of it we realize we have incorrect data. We trace it to users having logged back it and done transactions. We had to use restricted users because there was 3-4 of us in there to run the tests as fast as possible. We now had to constantly monitor the connections and keep killing them for 2 hours until we were done.
Now the only safe way we have is to pay ultra-overtime for the consultants which they don't want to do anyways or run in single user and shut down the application for 3-6 hours... which means 300+ <wo>man hours lost.
The correct way would be to have users in data reader_writer group and have a way to kick 'em of of the system when we need to.
I don't mind giving dbo for the consultants since they actually need it most days. But even that could be improved.
TIA.
April 15, 2011 at 11:21 am
Ninja's_RGR'us (4/15/2011)
Thanks a million. Any way I can provide more feedback if / when I find bugs? . . .
I pinged them and asked them for permission to give you their e-mail. As they are in Hyderabad, I would expect their reply by late Sunday.
April 18, 2011 at 12:44 pm
MS contact info sent via private mail.
December 28, 2015 at 7:35 am
Thanks for the reminder about this.
December 28, 2015 at 7:50 am
I think I'm going to have to disagree with Steve about SQL injection attacks not being T/SQL's fault.
I mean, T/SQL allows SQL code to have concatenation of statements seperated by a semi-colon, no white space rules *at all*, and a number of other syntactic sins that are simply unforgivable in a modern language.
So *of course* T/SQL is going to be vulnerable to SQL injection, it's pretty much a given.
The whole "only use stored procedures and always sanitize parameters" is a direct consequence of T/SQL syntax idiocy. It's a direct violation of separation of code and data.
If you're expecting data in your parameters there's NO EXCUSE for getting code. None. It all comes back to T/SQL (and SQL in general) mixing of code and data.
And that's the failing of the language at a design level. It goes beyond principle of least privilege, destroying the possibility of even having the very concept!
January 3, 2016 at 4:31 pm
I guess I'm in total disagreement with Roger Plowman.
T-SQL is not the only dialect of SQL that is vulnerable to injection attacks if the application is free to hand it any SQL with embedded constants that it chooses, instead of being required to pass those constants as parameters separate from the code (using either prepare statements or stored procedures (where the prepared statements or stored procs don't construct dynamic SQL from the parameters). Indeed I don't believe that any dialect of SQL (whether supported by MS, IBM, Oracle, or someone else) is free from this problem, although some versions of MySQL have some potentially useful (but put perhaps also potentially destructive of performance and modularity) restrictions. So I'm inclined to believe that any database server that supports SQL is vulnerable to injection attacks in an environment where the application does not use the mechanisms provided by the server to avoid such attacks.
Tom
January 4, 2016 at 6:49 am
TomThomson (1/3/2016)
I guess I'm in total disagreement with Roger Plowman.T-SQL is not the only dialect of SQL that is vulnerable to injection attacks if the application is free to hand it any SQL with embedded constants that it chooses, instead of being required to pass those constants as parameters separate from the code (using either prepare statements or stored procedures (where the prepared statements or stored procs don't construct dynamic SQL from the parameters). Indeed I don't believe that any dialect of SQL (whether supported by MS, IBM, Oracle, or someone else) is free from this problem, although some versions of MySQL have some potentially useful (but put perhaps also potentially destructive of performance and modularity) restrictions. So I'm inclined to believe that any database server that supports SQL is vulnerable to injection attacks in an environment where the application does not use the mechanisms provided by the server to avoid such attacks.
I find myself agreeing with you in part. I should nuance my position by saying *all* SQL is equally guilty of T/SQL's sins.
I do stand by much of what I said, in that it is trivial to create injection attacks based on the *syntax* allowed in SQL (all SQLs are equally guilty).
Python the language is also capable of dynamic execution of code, but in that case it's much more explicit, requiring a particular call (vaguely like 'do exec' in T/SQL).
SQL, on the other hand, makes it hard to *avoid* accidental code execution inside parameters...
January 4, 2016 at 6:57 am
roger.plowman (1/4/2016)
SQL, on the other hand, makes it hard to *avoid* accidental code execution inside parameters...
It's not hard at all.
Always parameterise queries (no, stored procs aren't required, parameterised SQL is), don't concatenate strings together and execute them. If you do just that, no sanitisation is required (though may be a good idea to avoid stuff like reflected XSS)
Queries like this cannot have accidental code execution because they're parameterised.
SELECT Col1, Col2 FROM SomeTable WHERE AnotherCol = @Parameter;
Front end languages have been able to call parameterised SQL for years. It's lazyness and lack of education that results in developers still concatenating strings together and executing them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2016 at 11:36 am
roger.plowman (1/4/2016)
I find myself agreeing with you in part. I should nuance my position by saying *all* SQL is equally guilty of T/SQL's sins.
Well, it's not just SQL. Any interface that allows more than one command at a time to be passed over the interface sffers from the problem: unless the component calling the interface passes parameters as separate components from the rest of the instruction and provides the type information needed to validate them, it is impossible for the code behind the interface to be safe. This has nothing at all to do with SQL syntax, or indeed the SQL language or any of its dialects - it is true for every interface which permits multiple commands. The mechanisms which SQL server (and all the other RDBMS out there) provide for the application to do this are as old as the hills. If the application chooses not to do it, and then constrcts calls from unchecked user input, there is nothing the server can do to prevent disaster. If the application passes the command sequence delete name from cattle_name where name = 'daisy';
delete employee where birthdate between '19000101' and '22000930'';
how is the sever supposed to know whether the application has fallen for an injection stunt or that is actually what the application intended? If the user typed
daisy'';
delete employee where birthdate between ''19000101''and ''22000930
and the application received that in it's buffer NameBuff and constructed the command by appending ' + NameBuff + ' to the partial command "delete name from cattle_name where " how is the server supposed to know that? the application screwed up, and blaming in the server or on the SQL language is just plain nonsense. The application should never do that, it should always either pass the partial command as one string and the parameter as a parameter string typed appropriately (whether using prepared statements or stored procedures to tell the server about the parameters and their types so that it can do the right thing) or undertake to validate the parameter string itself to ensure that concatenating it to the partial command string can do no harm (and in my opinion anyone who does the latter is overfond of doing things the hard way instead of getting them right the easy way).
To put it more simply, I might say "It's up to the application not to tell the server to destoy the data; it's not up to the server to second gues the application and refuse to do what the application tells it (except that it will not allow the server to violate constraints included in the schemata)".
I do stand by much of what I said, in that it is trivial to create injection attacks based on the *syntax* allowed in SQL (all SQLs are equally guilty).
Well, it's still pure nonsense whether you stand by it or not. Suppose I write a valid cobol programme that will destroy some data and pass it to a cobol interpreter. Is it a fault of SQL syntax (note that there is no SQL involved in this exploit) that this COBOL injection exploit works and destroys data? Is it a fault of COBOL syntax? No. it's simply that the cobol interpreter has been told to do the wrong thing. do you think it's a fault of VBS syntax that if I pass CSCRIPT some VBS it executes it? If not, why do you think it's a fault of SQL that if I pas an SQL interpreter some SQL it interprets it?
Python the language is also capable of dynamic execution of code, but in that case it's much more explicit, requiring a particular call (vaguely like 'do exec' in T/SQL).
What has dynamic execution got to do with anything? That is not what is happening here. The commands are built by the app before the SQL system sees them, they are just interpreting the static SQL that they are given. Of course there are extra ways of getting into trouble when you use dynamic code (in Python as well as in SQL, there's no difference) , but that's not what's happening in injection attacks.
SQL, on the other hand, makes it hard to *avoid* accidental code execution inside parameters...
It is not possible to have accidental code execution inside parameters in SQL. If you have decided not to treat them as parameters but pass them as part of the executed SQL injection can happen but that's your deliberate decision not something accidental, and it can only happen because you have chosen to make them NOT parameters but code.
Of course, it will be possible to have injection if the writer of the SQL code chooses to use something as part of a command, when it should be a parameter; but of course exactly the same applies to Python (indeed to any language with dynamic execution).
To summarise it all: if the application passes as code (to something which executes code for it) only stuff that is safe to use as code, nothing unwanted will happen. If it passes as code stuff which is unsafe for use as code, things will go wrong whether the language is SQL or something else. That's not a fault of SQL systems, it's a fault of command interpreters generally.
Tom
January 19, 2016 at 4:16 am
GilaMonster (1/4/2016)
roger.plowman (1/4/2016)
SQL, on the other hand, makes it hard to *avoid* accidental code execution inside parameters...It's not hard at all.
Always parameterise queries (no, stored procs aren't required, parameterised SQL is), don't concatenate strings together and execute them. If you do just that, no sanitisation is required (though may be a good idea to avoid stuff like reflected XSS)
Queries like this cannot have accidental code execution because they're parameterised.
SELECT Col1, Col2 FROM SomeTable WHERE AnotherCol = @Parameter;
Front end languages have been able to call parameterised SQL for years. It's lazyness and lack of education that results in developers still concatenating strings together and executing them.
Spot on for me.
It is only required when dynamic execution is used, however, it is best practice to always avoid the possibility because someone might add dynamic execution. All .NET languages are susceptible if they use dynamic execution. It is just used a lot less.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 19, 2016 at 6:37 am
Which is my entire point.
SQL (in most of its variants) makes it stupidly easy to SQL inject. We can control our own code, but what about a third party product? We don't control that, or have the ability to demand to examine their code--nor is that particularly helpful even if we could.
Stored procedures (and no dynamic SQL in them) are the way to go, but this all comes back to a fundamental flaw in SQL syntax.
January 19, 2016 at 7:10 am
roger.plowman (1/19/2016)
Stored procedures (and no dynamic SQL in them) are the way to go
No, they're not necessary. Parametrising calls is necessary. That's all.
If the app developer doesn't know what they're doing and concatenates input together to make the stored procedure call (instead of using a parameterised call), a stored procedure with no dynamic SQL can still result in injection.
Example C#ish (I forget exact syntax)
DBCommand.CommandType = "Text";
DBCommand.Command = "EXEC MyStoredProcWithNoDynamicSQL @Parameter = " & txtUserInput.Text;
DBConnection.Execute(DBCommand);
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 8:19 am
GilaMonster (1/19/2016)
roger.plowman (1/19/2016)
Stored procedures (and no dynamic SQL in them) are the way to goNo, they're not necessary. Parametrising calls is necessary. That's all.
If the app developer doesn't know what they're doing and concatenates input together to make the stored procedure call (instead of using a parameterised call), a stored procedure with no dynamic SQL can still result in injection.
Example C#ish (I forget exact syntax)
DBCommand.CommandType = "Text";
DBCommand.Command = "EXEC MyStoredProcWithNoDynamicSQL @Parameter = " & txtUserInput.Text;
DBConnection.Execute(DBCommand);
I agree with your sentiments here but that is not the appropriate way to call a stored procedure from .NET as the CommandType of "Text" is dynamic execution whereas a CommandType of "StoredProcedure" will only execute a stored procedure and forces the use of specifying parameters e.g. (off the top of my head)
DBCommand.CommandType = "StoredProcedure";
DBCommand.Command = "MyStoredProcWithNoDynamicSQL"
DBCommand.Command.Parameters.Add(new SqlParameter("@Parameter", txtUserInput.Text));
DBConnection.Execute(DBCommand);
...and hope that the stored procedure does not execute dynamic code or sanitises the parameter (should be checked here too).
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 19, 2016 at 8:27 am
Gary Varga (1/19/2016)
I agree with your sentiments here but that is not the appropriate way to call a stored procedure from .NET.
No kidding, since I posted it as a bad example.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 8:38 am
GilaMonster (1/19/2016)
Gary Varga (1/19/2016)
I agree with your sentiments here but that is not the appropriate way to call a stored procedure from .NET.No kidding, since I posted it as a bad example.
[Gaz grabs coat and exits stage left...] Doh!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply