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

Use hibernate if you can, perhaps via RMI from delphi. Although it's java centric, it almost completely insulates the programmer from the underlying DB, and handles the issues you've mentioned and a whole lot more.

btw, to answer your question re double quotes, to save a value which contains double quotes, escape them as doubled double quotes, eg

This is "my" text

would be saved as

"This is ""my"" text"

Answer 2

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 3

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.

Answer 4

You can take a look also here Delphi - prevent against SQL injection , there are some indication/examples.