5

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?

Toon Krijthe
  • 52,876
  • 38
  • 145
  • 202
Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
  • 1
    What kind of user input do you mean? For example, if the user gives you queries to run then you've got a lot of work ahead of you. In addition to escaping special characters, as you've mentioned, you'll also need to watch out for security issues such as SQL injection, and you'll probably also want to limit the set of acceptable statements (for example, don't allow DDL.) On the other hand, if the user just gives you data and your application uses ODBC to write the data to the database, then you don't have to do anything because the data and your SQL statements don't mix. – Ciaran Keating Jun 01 '11 at 07:57
  • 1
    possible duplicate of [Delphi - prevent against SQL injection](http://stackoverflow.com/questions/6000648/delphi-prevent-against-sql-injection) – Johan Jun 01 '11 at 08:58
  • ODBC will **not** make it future proof, it will make it slow, strip you of options and make it buggy. Any number of alternatives will do, ZEOS can connect to about 10 databases, so can ADO. – Johan Jun 01 '11 at 22:03
  • 1
    @Johan: We'd need to know a lot more about Mawg's project before we could determine that ODBC will be appreciably slower than some specific alternative. It's true that ODBC will reduce the options, but again we'd need to know more about the project in order to know whether this is likely to be a problem. And while it's true that ODBC is kind of complicated to implement, I'd hesitate to say bluntly that it would make the project buggy. Finally, ODBC may indeed make it easier to switch to a different DBMS, pretty much the same as any other abstraction will. – Ciaran Keating Jun 01 '11 at 23:19
  • +1 thanks. ODBC it will have to be. I have no choice. If I want to have it accepted I am told that I will need to support multiple databases. – Mawg says reinstate Monica Jun 03 '11 at 03:35

3 Answers3

8

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.

RRUZ
  • 134,889
  • 20
  • 356
  • 483
  • +1 I am trying to follow the example from http://docwiki.embarcadero.com/CodeExamples/en/ADOQuery_%28Delphi%29 but I get an exception about '_latin1' I will post another question asking help on that. – Mawg says reinstate Monica Jun 05 '11 at 04:42
8
  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.
da-soft
  • 7,670
  • 28
  • 36
3

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"
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • -1, first ODBC, than hibernate, how many levels of indirection do you want, just use params and native code. – Johan Jun 01 '11 at 22:01