SQL Injection Prevention: What Actually Works
The most effective SQL injection prevention is to stop building queries by gluing user input into strings: use parameterized queries (prepared statements) for every value, an allowlist for anything that cannot be a parameter (like ORDER BY), and a database account that can only do what your app needs.
This post is for developers and site owners who already know what SQL injection is and want to fix it in their own code. You will get a ranked fix list, short C# and Spring Boot examples, a safe way to handle sorting, and a quick test to confirm the fix worked.
SQL injection happens when user input changes the meaning of a database query instead of just being data inside it. OWASP lists injection as a major web risk category in its 2021 Top 10 edition, and its SQL Injection Prevention Cheat Sheet lists prepared statements as the first defense, and also covers allowlist validation and least privilege.
The ranked fix list
Do these in order. The first one fixes most cases on its own. The rest cover what it cannot.
| Rank | Fix | What it stops | Effort |
|---|---|---|---|
| 1 | Parameterized queries (prepared statements) | Injection through any value (search box, login, ID in URL) | Low |
| 2 | Allowlist for ORDER BY, column and table names | Injection through identifiers that cannot be parameters | Low |
| 3 | Least-privilege database account | Limits the damage if a bug slips through | Medium |
| 4 | Safe errors and input checks | Stops leaking details, rejects obviously bad input | Low |
| 5 | Test the fix | Checks the change works | Low |
A web application firewall (WAF, a filter in front of your site) can help as a backup. It is not a fix. The bug is still in your code.
Fix 1: Use prepared statements for every value
A prepared statement (also called a parameterized query) sends the SQL text and the user's data to the database separately. The database treats the data as data, never as SQL. That is why prepared statements prevent SQL injection, with one condition: you must put the input in a parameter, not in the query string.
This is unsafe (do not copy):
var sql = "SELECT Id, Name FROM Customers WHERE Email = '" + email + "'";
The input x' OR '1'='1 turns that into a query that returns every customer.
C# (ADO.NET with Microsoft.Data.SqlClient)
using System.Data;
using Microsoft.Data.SqlClient;
const string sql = "SELECT Id, Name FROM Customers WHERE Email = @email";
using var conn = new SqlConnection(connectionString);
using var cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 256).Value = email;
conn.Open();
using var reader = cmd.ExecuteReader();
The query text is a constant. The user's value only travels through @email. If you use Entity Framework Core, FromSqlInterpolated($"... {email}") also parameterizes for you, but FromSqlRaw with string concatenation does not. See Microsoft's SQL injection guidance.
Spring Boot (JdbcTemplate)
String sql = "SELECT id, name FROM customers WHERE email = ?";
List<Customer> rows = jdbcTemplate.query(
sql,
(rs, i) -> new Customer(rs.getLong("id"), rs.getString("name")),
email
);
The ? is a placeholder. Spring binds email as a value. Named placeholders work too with NamedParameterJdbcTemplate (:email). Details are in the Spring JDBC docs.
Spring Data JPA
@Query("SELECT c FROM Customer c WHERE c.email = :email")
List<Customer> findByEmailAddress(@Param("email") String email);
Derived methods like findByEmail(String email) are parameterized automatically. The risky pattern is building a JPQL or native query string with + or String.format and passing it to createQuery or createNativeQuery.
Plain Java JDBC
try (PreparedStatement ps = conn.prepareStatement(
"SELECT id, name FROM customers WHERE email = ?")) {
ps.setString(1, email);
try (ResultSet rs = ps.executeQuery()) { /* read rows */ }
}
Note that conn.createStatement() plus string concatenation is not a prepared statement, even if you later call it "prepared" in a comment.
Fix 2: Use an allowlist for ORDER BY
SQL injection in an ORDER BY clause is a common leftover after teams "fix" the rest. Sort fields like ?sort=price&dir=desc look harmless, but you cannot bind identifiers (such as column names) or the sort direction (ASC and DESC) as parameters. So a placeholder will not work here, and developers fall back to concatenation.
The fix is an allowlist: a fixed list of values you accept. Anything else gets a safe default. The user never supplies SQL text. They only pick a key from your list.
C#
var sortColumns = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
{
["name"] = "Name",
["price"] = "Price",
["created"] = "CreatedAt"
};
var column = sortColumns.TryGetValue(sort ?? "", out var c) ? c : "CreatedAt";
var direction = string.Equals(dir, "desc", StringComparison.OrdinalIgnoreCase) ? "DESC" : "ASC";
var sql = $"SELECT Id, Name, Price FROM Products WHERE Category = @cat ORDER BY {column} {direction}";
Spring Boot
private static final Map<String, String> SORT_COLUMNS = Map.of(
"name", "name",
"price", "price",
"created", "created_at"
);
String column = SORT_COLUMNS.getOrDefault(sort == null ? "" : sort.toLowerCase(Locale.ROOT), "created_at");
String direction = "desc".equalsIgnoreCase(dir) ? "DESC" : "ASC";
String sql = "SELECT id, name, price FROM products WHERE category = ? ORDER BY " + column + " " + direction;
List<Product> rows = jdbcTemplate.query(sql, productMapper, category);
The string you concatenate comes from your map, never from the request. Values still go through ?. The same trick works for table names and column lists. For LIMIT and OFFSET, bind the values as parameters or parse them as integers. If you pass user-supplied Sort objects to native queries in Spring Data, map them through an allowlist as well.
Fix 3: Give the app a least-privilege database account
Least privilege means the app's database login can do only what the app needs. If an injection bug survives, this limits what an attacker can read or change.
Bad signs: the app connects as root, sa, postgres, dbo or the database owner. Better: a dedicated login with only the permissions it uses. Example for PostgreSQL:
CREATE ROLE app_web LOGIN PASSWORD 'use-a-long-random-secret';
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE shop FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO app_web;
GRANT USAGE ON SCHEMA public TO app_web;
GRANT SELECT, INSERT, UPDATE ON customers, orders, products TO app_web;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_web;
The two REVOKE lines matter. Every PostgreSQL role is part of the built-in PUBLIC group. On PostgreSQL 14 and earlier, PUBLIC can create tables in the public schema by default, so without the first line app_web could create tables too. PostgreSQL 15 and later already remove that default, so the line is harmless there. The second line removes the default access PUBLIC has to the database, so any other role that relied on it will need its own GRANT CONNECT.
Notice what is missing: no DROP, no CREATE, no DELETE unless the app truly deletes, and no access to tables like admin users or billing exports. Run schema migrations with a separate, more powerful account that the running site never uses. SQL Server and MySQL have the same idea with GRANT statements.
This does not stop injection. It makes a successful one smaller.
Fix 4: Hide database errors and reject bad input
These are helpers, not the main defense.
- Return generic errors. Show "Something went wrong" to visitors and log the real SQL error on the server. Raw database errors help attackers map your queries.
- Validate by type. If an ID must be a number, parse it as a number. In Spring, declare
@PathVariable long id. In C#, useint.TryParseor typed route constraints like{id:int}. - Do not rely on "escaping" or blocklists. Stripping quotes or words like
DROPis easy to bypass and breaks real names like O'Brien.
Fix 5: Run a quick test to confirm the fix worked
Test on a staging copy or a system you own, never someone else's site. Use fake data.
- Find the risky code. Search your project for queries built with string joining:
grep -rniF '$"select' --include='*.cs' .
grep -rniE '(select|insert|update|delete) .*" *\+' src/
Each hit is a place to read carefully. Not every hit is a bug, and these searches will miss some (multi-line strings, string.Format, SQL built in a variable earlier), so also review every raw-query call by hand.
- Send a single quote. Request
/search?q=O'Brien. Before the fix, many apps return a 500 error or a database message. After the fix, you should get a normal result (usually "no results") and no error.
- Send an always-true condition. Request
/search?q=x' OR '1'='1. Fixed code treats it as literal text and returns nothing, not every row.
- Test sorting. Request
/products?sort=notacolumn&dir=sideways. The page should load with your default order or return a clean 400 error. It should never show a SQL error.
- Run an automated scan against staging. For example, with the open-source tool sqlmap:
sqlmap -u "https://staging.example.com/search?q=test" --batch --level=1 --risk=1
A clean result means it found nothing, not that nothing exists. Scanners miss logic and context. This is one reason a human tester still matters: see manual vs automated penetration testing.
- Keep a regression test. Add one automated test per endpoint that sends
'and checks for a non-500 response, so a future change cannot quietly undo the fix.
What fixed code looks like in a review
Use this as a checklist when reviewing a pull request:
- Every SQL string is a constant, or built only from allowlisted pieces.
- Every user value goes through a parameter (
@name,?or:name). - No
FromSqlRaw,createNativeQueryorcreateStatementcalls with concatenated input. - The app does not connect as an admin or owner account.
- Errors shown to users are generic.
Not sure whether your site has other weak spots? The guide to whether your website is hackable walks through the common ones, and the free website security check gives a passive outside look at a domain you own.
Key takeaways
- Use parameterized queries for every value. This is the single biggest SQL injection fix.
- Placeholders cannot protect ORDER BY or column names. Map user choices to a fixed allowlist instead.
- Connect with a least-privilege database account, and keep migrations on a separate one.
- Verify the fix with a single quote, an always-true input, a bad sort value and a scan of staging.
- Do not trust escaping, blocklists or a WAF as your only defense.
Want a person to check your work?
A scanner can confirm the easy cases. A human tester can find the odd ones, like a sort parameter or a hidden API call you forgot about. Bug Circuit's Essential plan is $49 one-time: a real engineer tests one website by hand and sends a written report with severity, evidence and fix steps. It is a focused small-site audit, not a compliance certificate or a multi-week enterprise pentest. Fixing is a separate service, quoted per issue, and you pay only if you agree.
A real human security engineer audits your whole site by hand and sends a full report: every issue, its severity, and the exact fix. From $49, with a 14-day money-back guarantee.