Correct way to escape input data before passing to ODBC

Question

I am very used to using MySQL and mysql_real_escape_string(), but I have been given a new PHP project that uses ODBC.

What is the correct way to escape user input in a SQL string?

Is addslashes() sufficient?

I would like to get this right now rather than later!

Infra

Answer 1

Instead of string escaping the PHP ODBC driver uses prepared statements. Use odbc_prepare to prepare an SQL statement and odbc_execute to pass in the parameters and execute the statements. (This is similar to what you can do with PDO).

Anabolite

Looks exactly like what I want. However, I'm completely new to prepared statements too. By using prepared statements, can I pass in special characters without worrying about escaping? e.g. to pass O'Brien in as a parameter, I just pass it in using odbc_exec into a prepared statement? – Infra

Yes, that's what prepared statements are for. They prevent SQL Injections as the DBMS handles the parameters for you. – Anabolite

Prepared statements are the most sensible approach. In practice, though, not all ODBC drivers support them. – Magnify

@ÁlvaroG.Vicario What would be the alternative to drivers which do not support those? – Literacy

@PacMani - I've had that problem myself and didn't find a satisfactory answer. As far as I know your only chance is reading technical docs to find out the DBMS mechanism and writing your own escape function. – Magnify

@ÁlvaroG.Vicario Okay, I just helped myself by simply replacing ' with '' in my case... not very secure, but the code is only used internally anyways... – Literacy

Prepared statements are great until you run into a bug such as: a parameter marker in a LIKE clause will cause a "String data, right truncation" if the bound value is too long (which is bogus; LIKE isn't supposed to generate that kind of error) (with PHP ODBC and SQL Server native driver on Windows, that is) – Gallinule