Information Security Stack Exchange is a question and answer site for information security professionals. It only takes a minute to sign up.
Sign up to join this community
In creating a blacklist function as part of our multi-pronged defense against SQL injection that includes parameterized queries and validating for length and type of input, we are creating a Blacklist function to check input from a .Net web form to run against SQL Server.
Does there HAVE to be a semicolon (;) or comment (--, /* */) for a successful SQL injection?
In the fully general case, absolutely not. For example, suppose your login system uses a SQL statement that looks like this: "SELECT TOP(1) userId FROM Users WHERE username = '" + userName + "' AND password = '" + hashedPass + "'" where userName is directly supplied by the user. If the attacker supplies ' OR 1=1 OR 'a'='a - which has no semicolons, comments, or even subqueries - then the entire query will be SELECT TOP(1) userId FROM Users WHERE username = '' OR 1=1 OR 'a'='a' AND password = 'f40a564107bad8ebf1b5ae73c350ebea' (assuming the hashed password is 'f40a564107bad8ebf1b5ae73c350ebea'). The AND operator in the WHERE clause takes precedence, so we gave it a nonsense value to check against ('a' will equal 'a' but the AND will still be false overall for probably every user since the password is a guess, and that's OK), and there probably isn't a username of '' in the DB, but the ... OR 1=1 OR ... part means that every single row will match the WHERE clause. The database will simply return the first row in the table, who is likely an admin of some kind. This example could be modified to instead log in as any specific user, still without needing their password.
As a general rule, while defense-in-depth is good, blocklists are basically worthless. Parameterize your queries (for SQL Server in particular, use Stored Procedures for a perf gain while you're at it) and use database-specific sanitization libraries for anything you can't parameterize or that might otherwise be concatenated into a SQL statement. Where practical, go ahead and validate types and lengths too. Trying to block specific characters is almost certainly going to be either too aggressive (blocking legitimate input) or too permissive (missing something that is potentially harmful) or even both; there is often overlap between "would be dangerous if interpreted as SQL code" and "might legitimately appear in user data".
In the best case, where you somehow have a perfect blocklist that doesn't break anything, it'll still fail if you forget to check input against it. That might sound like a silly concern but both parameterization and escaping are fully-sufficient defenses when used correctly, so for the blocklist to be relevant you'd have to already be forgetting something. Your odds of forgetting every security control except the blocklist seem low enough that its expected value approaches zero anyhow.
