Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?

by

First of all, it’s just bad practice. Input validation is always necessary, but it’s also always iffy.
Worse yet, blacklist validation is always problematic, it’s much better to explicitly and strictly define what values/formats you accept. Admittedly, this is not always possible – but to some extent it must always be done.
Some research papers on the subject:

Point is, any blacklist you do (and too-permissive whitelists) can be bypassed. The last link to my paper shows situations where even quote escaping can be bypassed.

Even if these situations do not apply to you, it’s still a bad idea. Moreover, unless your app is trivially small, you’re going to have to deal with maintenance, and maybe a certain amount of governance: how do you ensure that its done right, everywhere all the time?

The proper way to do it:

  • Whitelist validation: type, length, format or accepted values
  • If you want to blacklist, go right ahead. Quote escaping is good, but within context of the other mitigations.
  • Use Command and Parameter objects, to preparse and validate
  • Call parameterized queries only.
  • Better yet, use Stored Procedures exclusively.
  • Avoid using dynamic SQL, and dont use string concatenation to build queries.
  • If using SPs, you can also limit permissions in the database to executing the needed SPs only, and not access tables directly.
  • you can also easily verify that the entire codebase only accesses the DB through SPs…

More Related Contents:

  1. What is SQL injection? [duplicate]
  2. How can prepared statements protect from SQL injection attacks?
  3. Can we protect against SQL-injection by writing Javascript code correctly? how? [closed]
  4. Add a column with a default value to an existing table in SQL Server
  5. How can I prevent SQL injection in PHP?
  6. SQL injection that gets around mysql_real_escape_string()
  7. Why do we always prefer using parameters in SQL statements?
  8. What are good ways to prevent SQL injection? [duplicate]
  9. How to Join to first row
  10. Examples of SQL Injections through addslashes()?
  11. How should I pass a table name into a stored proc?
  12. Is SQL IN bad for performance?
  13. Calendar Table – Week number of month
  14. How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
  15. Pivot using SQL Server 2000
  16. Does using parameterized SqlCommand make my program immune to SQL injection?
  17. How to get the first and last date of the current year?
  18. Restrict an SQL Server connection to a specific IP address
  19. Can parameterized statement stop all SQL injection?
  20. How can I generate a temporary table filled with dates in SQL Server 2000?
  21. Is htmlspecialchars enough to prevent an SQL injection on a variable enclosed in single quotes?
  22. Protecting against SQL injection in python
  23. How can I avoid SQL injection attacks in my ASP.NET application?
  24. How Can I Sort A ‘Version Number’ Column Generically Using a SQL Server Query
  25. Grant SELECT permission on a view, but not on underlying objects
  26. Perform regex (replace) in an SQL query
  27. WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value
  28. Find last sunday
  29. RegEx to Detect SQL Injection
  30. ‘^M’ character at end of lines

Leave a Comment