March 2, 2026 at 12:00 am
Comments posted to this topic are about the item Guarding Against SQL Injection at the Database Layer (SQL Server)
March 2, 2026 at 8:02 am
The solution to SQL injection is to place all user identification and security in the database.
Using a combination of table and row level security is possible to define exactly which data the users in any role can select or update. As far as a particular group of users is concerned this defined subset of the database is the database.
So an application can use whatever SQL it likes, including dynamic SQL, without compromising the data or the structure of the database in any way.
March 2, 2026 at 8:40 am
You’re right, putting proper user identification and security in the database is a strong solution. Using table and row level security properly can lock things down very tightly and, in theory, let the application do what it wants without exposing data or structure.
The difficulty is that a lot of systems in production today were built years ago, when security was not always designed in from the start. Many still use shared SQL accounts with broad permissions, and access control is handled mostly in the application layer.
Moving those systems to granular role-based access and row level security can mean major refactoring. It often involves changes to authentication, permissions, stored procedures, and sometimes the overall architecture. That is the right direction, but it is rarely something that can be done quickly.
I still see SQL injection in the wild quite often, especially in legacy estates. For many organisations, strengthening database-layer security has to happen step by step rather than all at once.
Completely agree with you on the end goal though.
March 3, 2026 at 11:40 am
Implementing the database level security would open up the database to flexible access independent of applications. This is one of the reasons that we have relational DBMSs to begin with.
This could be done without any impact on existing applications. The applications could follow on, though the first step would be to severely restrict the access rights of the generic login used by each application.
March 3, 2026 at 1:25 pm
You are right however, Restricting database login rights reduces the impact surface of a successful SQL injection attack but does not prevent injection itself. Prevention requires parameterized queries and safe query construction at the application layer. Database-level controls provide defence-in-depth, not primary mitigation.
March 3, 2026 at 1:59 pm
Database level security offers security of the data against SQL injection completely independently of how the database is accessed either through applications or ad-hoc queries. There need be no duplication of the security logic in different applications, substantially reducing errors and the workload of application developers.
The remaining risk of SQL injection is denial of service by, for example, starting a Cartesian product of the largest tables in the database inside a serialisable transaction. Some other methods would be necessary to protect against this, but the data would still be safe.
March 5, 2026 at 9:35 pm
Implementing the database level security would open up the database to flexible access independent of applications. This is one of the reasons that we have relational DBMSs to begin with.
This could be done without any impact on existing applications. The applications could follow on, though the first step would be to severely restrict the access rights of the generic login used by each application.
The policies I've worked with for a long time do not allow users to have any update type access to a database with their regular login credentials. In fact, we typically limit direct user access to data completely because too many users in the past used MS access a sa tool and locked up the database. If users can directly access data for query, it's done on a copy of the data. If users have update access, how do you stop them from randomly changing data outside an application? Users cannot be trusted to not break things on a regular basis.
The solution to SQL injection is to place all user identification and security in the database.
Using a combination of table and row level security is possible to define exactly which data the users in any role can select or update. As far as a particular group of users is concerned this defined subset of the database is the database.
So an application can use whatever SQL it likes, including dynamic SQL, without compromising the data or the structure of the database in any way.
The solution to SQL injection is to write applications in a manner that makes injection impossible. And set up security to prevent users from doing anything outside an application.
March 5, 2026 at 11:10 pm
The solution to SQL injection is to write applications in a manner that makes injection impossible. And set up security to prevent users from doing anything outside an application.
There might be hundreds or even thousands of SQL queries in an application that might have been worked on by dozens of programmers over time. The chances that every programmer has written every piece of code to avoid SQL injection is pretty close to zero.
The risk to data security is too great to rely on such methods.
March 5, 2026 at 11:15 pm
If users have update access, how do you stop them from randomly changing data outside an application? Users cannot be trusted to not break things on a regular basis.
By having constraints in the database. The constraints prevent users from doing anything that they are not allowed to do or to do anything that would damage the integrity of the data.
If you leave the constraints in the applications then every application will have to re-implement these constraints. The integrity of the database will be compromised and code reduplicated.
What applications do is to provide some constraint on input so that users can only choose valid entries but this shouldn't be where the data integrity rules are implemented. The constraints in the user interface should be directly derived from the constraints in the database - something that can be achieved automatically without the application programmer having to be concerned with it. Otherwise you are reduplicating logic and risking data quality. All the application is doing is applying the already defined constraints in the database pre-emptively so the user is guided to making correct input.
Putting the constraints in the database is the only way to ensure the integrity of the data and rendering SQL injection completely incapable of violating these constraints.
March 6, 2026 at 6:26 pm
Just to be clear, this solution only works if the code is using stored procs. If the code is building its own dynamic SQL and sending that to the database, this solution won't catch it and in my experience, it is the middle-tier code assembling raw SQL statements where I've found the vast majority of SQL injection vulnerabilities. It can clearly happen when the middle-tier code tries to assemble the call to the stored procs but my experience has been that this is less often. Still, for those times, as mentioned in the article, I can see how this would be an additional layer of protection.
March 9, 2026 at 7:55 pm
If users have update access, how do you stop them from randomly changing data outside an application? Users cannot be trusted to not break things on a regular basis.
By having constraints in the database. The constraints prevent users from doing anything that they are not allowed to do or to do anything that would damage the integrity of the data.
If you leave the constraints in the applications then every application will have to re-implement these constraints. The integrity of the database will be compromised and code reduplicated.
What applications do is to provide some constraint on input so that users can only choose valid entries but this shouldn't be where the data integrity rules are implemented. The constraints in the user interface should be directly derived from the constraints in the database - something that can be achieved automatically without the application programmer having to be concerned with it. Otherwise you are reduplicating logic and risking data quality. All the application is doing is applying the already defined constraints in the database pre-emptively so the user is guided to making correct input.
Putting the constraints in the database is the only way to ensure the integrity of the data and rendering SQL injection completely incapable of violating these constraints.
If you give me update and insert on a database for, say, an accounting application, there is nothing you can do with constraints to stop me from creating a vendor, creating an invoice and paying that vendor as long as I don't violate the constraints.
A regular user ID should never have the ability to directly change or insert data. A SOX audit will eat you alive if that happens. If you think SQL injection is an issue, just wait until your users find out they can change data without going through the application.
March 9, 2026 at 8:20 pm
If you give me update and insert on a database for, say, an accounting application, there is nothing you can do with constraints to stop me from creating a vendor, creating an invoice and paying that vendor as long as I don't violate the constraints.
A regular user ID should never have the ability to directly change or insert data. A SOX audit will eat you alive if that happens. If you think SQL injection is an issue, just wait until your users find out they can change data without going through the application.
If only users who belong to the bookkeeping role can insert into the vendor table then the problem is solved without needing to put any of the logic in the application. Likewise if a sales person is only allowed to see their sales data then row level security handles this. You can do everything you need with constraints to protect the data in the database from unauthorised access. Not only can you do that, but I would argue that you must do that. Otherwise your database is not properly secured.
Generic logins to the database are a major security risk. It is using generic logins to access the database that open the door to attacks using SQL injection. If an attacker gets hold of the generic login then they can do anything they want. If you only use specific UserIDs and define the constraints correctly then every user can only see the data they are authorised to see. In addition you can see exactly which user did what in the database.
Why do you believe the application is more secure than the database? If you have two different applications that access the accounting data then the whole of the security layer has to be duplicated.
The SOX auditors would be more that happy to see that whatever application accesses the database (even if that application happens to be Excel) then users can only see the data to which they are authorised. The risks of putting the constraints in an application are far higher than putting them in the database.
I see the purpose of an application as being to make working with the data more user friendly. Data security does not belong in an application.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply