SQL Injection Prevention: What Actually Works

By Bug Circuit Security Team
SQL Injection Prevention: What Actually Works

Is your own website affected? Check it free in 2 minutes.

Passive outside check only. Nothing is attacked, no signup.

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.

RankFixWhat it stopsEffort
1Parameterized queries (prepared statements)Injection through any value (search box, login, ID in URL)Low
2Allowlist for ORDER BY, column and table namesInjection through identifiers that cannot be parametersLow
3Least-privilege database accountLimits the damage if a bug slips throughMedium
4Safe errors and input checksStops leaking details, rejects obviously bad inputLow
5Test the fixChecks the change worksLow

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#, use int.TryParse or typed route constraints like {id:int}.
  • Do not rely on "escaping" or blocklists. Stripping quotes or words like DROP is 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.

  1. 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.

  1. 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.
  1. 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.
  1. 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.
  1. 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.

  1. 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, createNativeQuery or createStatement calls 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.

Want certainty, not guesswork?

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.

Frequently asked questions

Can SQL injection be prevented by using PreparedStatement?
Yes, for values, as long as the user input goes into a `?` parameter and not into the query string. A PreparedStatement built with string concatenation is still vulnerable. It does not cover column names or ORDER BY, which need an allowlist.
How do you stop SQL injection in an ORDER BY clause?
You cannot bind column names or ASC and DESC as parameters, so use an allowlist. Map the user's choice (like `price`) to a column name from a fixed list in your code, and use a safe default for anything else. Never paste the raw request value into the SQL.
What is the best SQL injection remediation?
Find every place that builds SQL with string concatenation and switch it to parameterized queries. Then allowlist identifiers like sort columns, reduce database account permissions, and retest. Fix the code first, since firewalls and filters only reduce risk.
Does an ORM like Entity Framework or Hibernate prevent SQL injection?
Mostly, when you use its normal query methods, because they parameterize values for you. The risk comes back when you use raw or native query features with concatenated strings, such as `FromSqlRaw` or `createNativeQuery`. Review those spots first.
How do I test if my SQL injection fix worked?
On a staging copy, send a single quote and an always-true input like `x' OR '1'='1` to each parameter, plus an invalid sort value. You should see normal results or clean errors, never a database error or extra rows. Then run a scanner such as sqlmap and add a regression test.

Keep reading

See what attackers see, for free

Run the free passive check on your website. No login, no impact on your site, results in seconds.

Ready for the full manual audit? See pricing

Published by Bug Circuit. Written with AI assistance and reviewed for accuracy before publishing.