[web] php odbc vs. SQL Injection

The only other function I know of that might help you is doubleval(); (I can't say I've worked much with ODBC except with Ms Access. obdc_prepare and odbc_execute are completely foreign functions to me). doubleval(); works similar to addaslashes(), but is used with floating point numbers instead of text. Other than that, I'd say you're on your own. I just don't think PHP was written with ODBC in mind.

There should be no need to fuss about with ODBC to use MySQL 3.x in PHP. In that case you can simply use mysql_real_escape_string on your variables to prevent SQL injection attacks. If you do need to use ODBC (portability?) then I suggest you write your own escape function and use that on your variables. I don't see how odbc_prepare can help you with any of this.

Yes, unfortunately I'm writing a site that will be used by people with an ODBC connection to an arbitrary database, usually MySQL or SQL Server, and I have to use only generic SQL and functions. It's a mess. I just began this job recently, and the PHP code I have to deal with is pretty horrible... When I got here, I could have entered something into the login and password boxes to destroy the database. I guess that's what you get (usually) when you use college students to write your code.



~BenDilts( void );

If generic SQL is all it will take, then using then applying a generic escape should be good enough. Best read the ANSI SQL specs to be sure, but I gather that addslashes() should do the trick quite nicely.

Just to be sure, wrap it in your own function so you can replace it later on. E.g:

function obdc_escape($s) {    return addslashes($s);}$sql = "UPDATE table SET field = '" . odbc_escape($value) . "'";

There are only two ways of doing this:

1. Use the *CORRECT* escaping function for the database in question.
2. Use parameterised queries. This may be done directly, or via PEAR DB (I generally recommend PEAR DB)

addslashes() is never acceptable, for any purpose whatsoever.

I suggest that you periodically sweep your source for addslashes() ensure it contains none. Ideally, disable addslashes() in php.ini, that way any pages which use it (obviously erroneously) will fail in an obvious fashion.

stripslashes() is equally a useless, pointless function and should never be called. If you're calling it, you're doing something wrong.

The problem, in general, with using ODBC is that some vendors have duff ODBC drivers, and there are ODBC drivers for some databases unsuitable for use with web applications (for example, MSAccess).

So MySQL and MSSQL might be acceptable, provided you stick to the common subset of syntax, which to be quite honest, isn't much.

You might want to do a delete with subselect (MSSQL), but that won't work in MySQL, or a delete with a join (MySQL), but that won't work in MSSQL.

---

EDIT: just noticed that odbc_escape is not a standard PHP function, it's defined by Sander above.

Mark

Disabling addslashes in the php.ini? It's a fucntion, not a setting. I think you are talking about the magic_quotes_gpc setting, which is indeed an evil thing. There's nothing wrong with manually invoking addslashes if you need it.

Quote:

Original post by Sander Disabling addslashes in the php.ini?

Yes. Using the disable_function facility.

addslashes is evil and should under no account be used for anything.

Quote:

It's a fucntion, not a setting.

I am aware of this.

Quote:

I think you are talking about the magic_quotes_gpc setting

magic_quotes_gpc and magic_quotes_runtime are also evil and must be disabled.

addslashes is not the correct way to escape anything - ever. Therefore, its use is incorrect.

stripslashes is even worse - it's usually used to attempt to recover from an incorrect usage of addslashes or magic quotes - therefore it's not useful. Most cases where people use it, it will cause data corruption (i.e. removing backslashes which are in your real data)

Mark

Quote:

Original post by markr Yes. Using the disable_function facility.

I didn't know that directive existed. That's why I thought you were referring to magic_quotes_gpc.

Great point on PEAR. I'd never heard of it before, but it looks fantastic.



~BenDilts( void );

There are a lot of things about PEAR that suck. It seems to use a variety of horrible hacks to work-around the lack of features in PHP, many of which have since been added.

The way error handling works is perverse - rather than using PHP's existing error mechanisms, it wraps it all up in its own messy layer - basically for compatibility with older PHP versions, I understand.

Normally I want to use libraries which either work, or fail loudly with big flashy errors than I can spot easily. PEAR's error handling is so complicated, I don't understand quite how to make this happen in all cases.

Also when I do enable maximum error handling, I sometimes see PEAR libraries throwing errors during normal execution that they shouldn't - this indicates poor testing.

Mark

Quote:

Original post by markr addslashes() is never acceptable, for any purpose whatsoever.

Since I use addslashes() alot I have to ask: What makes addslashes() so evil?

Quote:

Original post by kanzler What makes addslashes() so evil?

addslashes itself isn't evil, in the same way as the chainsaw used in the texas chainsaw massacre wasn't evil.

The reason that I find addslashes objectionable, is that lots of PHP programmers seem to think that it's the correct way of escaping strings into SQL. It is not.

Each database has its own method of escaping strings for SQL. In some cases (MySQL especially), you need an established database connection before you can correctly escape a string, as it is (potentially) dependent on the database's encoding settings. There is a well documented function for doing this in MySQL.

There may be some case where you can legitimately use addslashes, but I haven't found one yet.

Mark