How to sanitize ODBC database input?

Question

I currently use MySql, but would prefer an ODBC solution to make it future proof.

How do I sanitize user input before passing it to an ODBC database ?

And, while I'm at it, I wrap my string in double quotes, e.g. "INSERT INTO VALUES(description) ""` - but what if the text itself contains a double quote?

Answer 1

Try using a parametrized SQL sentence

like this.

INSERT INTO MyTable (Field1,Field2) VALUES (:Param1,:Param2)

check this article from embarcadero for more info about how use parameters Using Parameters in Queries.

Answer 2

1. ODBC is not an optimal way to work with MySQL. Even if you need to support few DBMS in the future, then you can consider multi-DBMS data access libraries, including dbExpress (comes with Delphi) and 3d party - AnyDAC (commercial), ZeosLib (freeware), etc.
2. If you need to substitute a string constant into a MySQL query, then you need to esacape the special characters or convert the string into hexadecimal representation. That protects you from possible SQL injection and syntax errors. But makes your query preparation more complex.
3. The best way - use parameters and submit literals as the parameter values. That is simple and safe.