Last night I had a relatively busy discussion with a person on another forum regarding SQL Injection Attacks. The person basically made the statement that using objects to execute dynamic sql was a bit of a security risk.

I quickly pointed out that using the StrongNameIdentityPermission would stop untrusted callers from using objects that built and executed TSQL.

Does anyone know what LLBLGen does internally to prevent SQL Injection?

I think LLBLGen uses SQLParam objects in its queries and is there for SQL Injection Save. I remember reading an article from M$ where they were advising people to use these Param Objects as they are SQL Injection save.

SQL Injection attacks mostly happen when you create a SQL Query by concatenating the params values into the sql string.

[i]'Select X.1, X.2
from X 
Where X.Y = ' + txtYValue.text[/i]

_ (The Book C# Design Patterns: A Tutorial - shows people how to create a Facade Pattern (Data Objects) with this method of concatenating People doing this type of thing are asking for SQL Injection)_

**Example of Injection attack as i understand it. **

[i]'Select X.1, X.2
from X 
Where X.Y = '1'Delete from X'[/i]

(Note the fake value followed by a close quote' infront of the SQL Injection)

LLBLGen uses ADO.Net and Param Objects and ADO.Net generates the following SQL.

exec sp_executesql N'SELECT [dbo].[X].[X1] AS [X1],[dbo].[X].[X2] AS [X2]
FROM [dbo].[X] WHERE ( [dbo].[X].[XY] = @Param1)', N'@Param1 int'

Another type of attack that is sometimes more seriouse is Java Script Injection Attacks Where Java script gets put into fields that get saved in the database. Once these field values get displayed in a web page the Java Script will get executed.

What alot of these java script injections do is to redirect the person to another website.

all functions use parameters, or the function isn't added This means that no user values are entering the query in any way.

A programmer can of course inject SQL into the query, as a programmer can also call format.exe or drop a table or add a backdoor so he can order a gazillion products for free.

So I think the definition what an Sql Injection attack is has to be defined clearly. I define it as: an attack from the outside, without using intentions added by the programmer (i.e. backdoor, code which is written to break). This means that if the programmer simply does his job, the code is still vulnerable due to an sql injection attack by this definition.

By using LLBLGen Pro code, such an attack is NOT possible as no value is added to the query as part of the SQL, only through parameters.

Ok, so lets take this scenario, even though it is not a true SQL Injection Attack.

Lets say that I have code generated an DB Generic Assembly, a DB Specific Assembly, and published this in a directory, along with the ORM Support Library and the DQE.

If a hacker got their hands on these 4 items, what could I do, as a developer to ensure that they could not create instances of my DataAccessAdapter, and keep them from passing their own IEntityBase2 objects to the adapter? I am assuming the most logical answer is to use the StrongNamedIdentityPermission.

Also, if a hacker has a copy of the ORM Support Library, and the DQE, what keeps them from gaining access to my data, except for the fact that they dont have credenitals to login to the sql box?

Devildog74 wrote:

Ok, so lets take this scenario, even though it is not a true SQL Injection Attack.

Lets say that I have code generated an DB Generic Assembly, a DB Specific Assembly, and published this in a directory, along with the ORM Support Library and the DQE.

If a hacker got their hands on these 4 items, what could I do, as a developer to ensure that they could not create instances of my DataAccessAdapter, and keep them from passing their own IEntityBase2 objects to the adapter? I am assuming the most logical answer is to use the StrongNamedIdentityPermission.

If a hacker gets access to the directory these items were stored in and can alter them, you're doomed. But that's easily solved: run the website under a user Foo and give only Foo access to the folder . It's a known thing: as soon as a hacker can access your files and can make their own code run inside your application, you've serious problems.

Also, if a hacker has a copy of the ORM Support Library, and the DQE, what keeps them from gaining access to my data, except for the fact that they dont have credenitals to login to the sql box?

Nothing, but the hacker's position is the same as me sitting here with a copy of the files and no access to your databases due to firewalls. I.e. : he misses an essential element for getting access. Step one of keeping hackers away is by not letting them get any access.

Devildog74 wrote:

Ok, so lets take this scenario, even though it is not a true SQL Injection Attack.

Lets say that I have code generated an DB Generic Assembly, a DB Specific Assembly, and published this in a directory, along with the ORM Support Library and the DQE.

If a hacker got their hands on these 4 items, what could I do, as a developer to ensure that they could not create instances of my DataAccessAdapter, and keep them from passing their own IEntityBase2 objects to the adapter? I am assuming the most logical answer is to use the StrongNamedIdentityPermission.

Also, if a hacker has a copy of the ORM Support Library, and the DQE, what keeps them from gaining access to my data, except for the fact that they dont have credenitals to login to the sql box?

IMHO getting hold of those files is the same as having access to ISQL / Query Analyzer - No Login account - No Data

If using Self Serv.. the hacker in theory could build your database structure - That is if you don't use a obverscator of some kind.

I think it is much more dangerous if the hacker gets hold of your config file - because that is where your connection string is kept - encrypted or not. But again...That is why you need a firewall

What we do:

We have 2 Servers, a webserver and a database server. The Webserver only has a limited SQL Account and only this account can access the database server. This rules is inforced on the database server security. A firewall on the database server has the rule to only allow webserver to access the database server. (That means if the hacker get the dll or account info - he is going to have to be working on the fisical webserver if he wants to get to the data)

I think i spoke to you (DevilDog) earlier this year about remoting... Management wants to add a Application Server in between the Web And Data Server. By having the same type of scaled down login / usage security you can make your system as water tight as a frogs arse

Thanks for the great feedback everyone. I have always been a beleiver in the security of LLBLGen.

I think the down fall of many applicaitons is a clear text password.

I just wanted to double check because the person I was having the debate with ensured me that if something had to create a TSQL string , wether it be an EXECUTE or sp_execsql that, that logic would inevitibly need to build the TSQL and was that open to SQL Injection.

Personally, I think the guy was dead wrong, but hey, what do I know. I tried explaining that the code would only ever be as secure as the person who wrote it and that using the StrongNamedIdentityPermission would guarantee that untrusted code would never be allowed to inject SQL.

Then I tried explaining that comparatively speaking, having a plaintext pwd and user name in the web.config was a bigger security hole than using secured objects.

Actually, the whole debate was really over, wether or not someone should use stored procedures to do sorting. My argument was that with good OOD, you dont need a stored procedure in the first place. My outlook was why write a stored proc, with a conditional argument, or a stored proc for ASC, and one for DESC, when you could just use an object that would cache an execution plan.

Then it was all down hill from there.

I just wanted to double check because the person I was having the debate with ensured me that if something had to create a TSQL string , wether it be an EXECUTE or sp_execsql that, that logic would inevitibly need to build the TSQL and was that open to SQL Injection.

Sounds like you should set Frans loose on this guy... he will teach him. but then again this guy sound as if he does not want to learn anything as he knows better.

logic would inevitibly need to build the TSQL

true, but the threat of sql injection comes from the maliciouse paramaters being concatenated into the TSQL - Frans is leaving the concat descision upto ADO.Net as he is passing the paramaters not as string but as parameter objects - ADO.Net's paramtes objects are SQL Injection save.