[GLLUG] Retrieving a row from mysql

[Jeremy Bowers]

Szymon Machajewski wrote:
> Jeremy,
> 
> I have to disagree with you on using the function always.
> 
> It will depend greatly on your database.  What if you have a database
> where you retrieve data not only with PHP, but with Crystal Reports and
> other tools.  What about the escape characters then ?
> You will see on the ODBC report: O\'Brien

Then you're not likely to use mysql_really_escape_characters. You're 
tied to mysql by using those functions, and you've already got work to 
do if you want to move to Crystal reports.

Using the wrong escaping/encoding function is always a bad idea. This 
does not mean you should not use them.

This is part of the reason I was surprised at PHP. The Python database 
interface actually provides a function that abstracts the database 
escaping rules themselves away, so you can go from MySQL to Oracle to 
Postgres and the escaping rules will follow correctly.

This is a weakness in your choice of language/libraries, and thus it is 
even more important for you to make sure you're escaping correctly. That 
requires more work then it should; sorry, I can't do much about that.

> For example you should have a sql_safe() function where you would
> define the escape characters you choose to use.  Then you would use
> $comments = sql_safe($comments) to reset each value before using in a
> query.
> What works sometimes is simply replacing single quotes with back ticks.
>  They look fine on the web page and in Crystal.
> 
> So there are no blanket solutions and there is always a trade off.

I have to admit to finding this statement sort of ironic, since you just 
described a correct blanket solution (if sql_safe takes its chars based 
on the type of connection you are using). It is *always* worth making 
sure your characters are correctly escaped relative to the environment 
you are in, or you will almost certainly eventually regret it and have a 
hard time fixing it.

(When dealing with the web, always make sure that:

* Special SQL characters like ' can be inserted into the database 
correctly, and if you can edit the entries, that the ' comes back out to 
the editing interface correctly, such that editing an entry and 
immediately hitting submit doesn't change the database entry.

* Do the same for *all of* &, &, and &. I've had trouble 
with every single one of those independently. These are *certain* to 
come up if you're allowing some sort of content editing, but even in 
other scenarios you'd be surprised where these pop up.

Again, if it's private, you can slack off on this... but don't fool 
yourself, you ARE slacking off, and you may end up paying for it at the 
worst time... or not, but it's a risk.)

Believe me, I'm trying to help; I've spent at least 20 hours in my life 
tracking down stupid escaping issues, probably MUCH more, rather then 
doing other interesting or productive work. (It's even *worse* when it's 
not your bug, because the bug is in closed-source outside of your 
control...) You do not want to develop bad habits in this regard. Ten 
minutes getting it right now will pay off handsomely.