[GLLUG] Retrieving a row from mysql
jerf at jerf.org
Tue Feb 17 12:09:34 EST 2004
Szymon Machajewski wrote:
> 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
> 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 &amp;. 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.
More information about the linux-user