Using mysql_real_escape_string without connecting to mysql - PHP

On 23/01/2008, Eric Butera <eric.butera@gmail.com> wrote:
> There isn't a reason to go and report a bug as their stuff works fine.

I would have filed a wish, not a bug. They are both filed in the
bugzillas that I'm familiar with. In any case, I'm not filing as I've
no account there and I'll not be filing many bugs for that software.
If someone else wants to file a wish, be my guest.

> If you know you have utf8 and all that jazz then fine. The only
> reason you should use mysql escaping is right before you put a value
> into the database. To put a value in the database you must have a
> connection. So this really is a non-issue in my opinion.

No, I sanitize the values, and only then I decide if the value (now
sanitized and safe to work with) should go to the database. And only
if it's going to the database do I open a connection.

> Look at mysqli or pdo and start working with prepared statements.

Thanks, I will take a look at those!

Dotan Cohen

http://what-is-what.com
http://gibberish.co.il
א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת

A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

On 23/01/2008, Jochem Maas <jochem@iamjochem.com> wrote:
> I can read, I saw 2 functions the first time. each function cleans *and* escapes.
>
> cleaning is filtering of input.
> escaping is preparing for output.
>
> 2 concepts.

I see your point.

> if the input needs to be stripped of html then it needs that regardless
> of the output vector. again removing or not-accepting input if it contains
> '--' is a question of filtering/validation ... besides which '--' is quite
> acceptable for data stored in a text field but not for a numeric one.

I'm not accepting "--" at all until someone can show me a real world
case where one would use it, without the intention of SQL injection.
How can it be escaped, anyway?

> filter each piece of data
> validate each piece of data
> escape each peice of data for each context in which it will be output.

I see that you have more experience than I!

> imho your functions are conceptually wrong and not very robust either -
> don't take it as a personal attack - I'm very sure if we sat down with *some*
> of my code the same critism could be made to more or lesser extent :-) ...
> "getting better all the time" as they sang once ;-)

I never thought that was a personal attack, not for a second. Rather,
I very much appreciate the time you take to explain to me my errors.
And I intend to learn from them. For the time being, I'll leave the
code as it is. However, for future projects, I will make a point of
separating the different functions. Thanks.

Dotan Cohen

http://what-is-what.com
http://gibberish.co.il
א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת

A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

Dotan Cohen wrote:
> On 23/01/2008, mike <mike503@gmail.com> wrote:
>>>> It would be Real Nifty (tm) if the MySQL API had a function that let
>>>> you specify the charset without a connection and did the escaping.
>>>>
>>>> Presumably you don't NEED a connection if you already know what
>>>> charset thingie you are aiming at...
>> I concur - it would be nice to have the capability to have a normal
>> string escape function and give it a character set. I mean we should
>> all be using utf-8 anyway, right?
>
> I'd be interested in hearing an argument against UTF-8, other than the
> disk space argument.
>
>> Right now I still use mysql_escape_string and it seems to work fine,
>> but it makes me nervous as everything else I use is mysqli and I know
>> it is not 100% compatible (just haven't had anything break it yet) -
>> but I hate having to have a connection handle open just to escape
>> things.
>
> I think it was here on this list that we saw an example of SQL
> injection despite the use of mysql_escape_string. Some funky Asian
> charset was used, no?

Nope.

This article explains all I think:

http://ilia.ws/archives/103-mysql_re...tatements.html

--
Postgresql & php tutorials
http://www.designmagick.com/

> Right now I still use mysql_escape_string and it seems to work fine,
> but it makes me nervous as everything else I use is mysqli and I know
> it is not 100% compatible (just haven't had anything break it yet) -
> but I hate having to have a connection handle open just to escape
> things.

If you need to escape something you're going to do a query aren't you?
Or am I missing something here?

--
Postgresql & php tutorials
http://www.designmagick.com/

Dotan Cohen wrote:
> On 23/01/2008, Jochem Maas <jochem@iamjochem.com> wrote:
>> I can read, I saw 2 functions the first time. each function cleans *and* escapes.
>>
>> cleaning is filtering of input.
>> escaping is preparing for output.
>>
>> 2 concepts.
>
> I see your point.
>
>> if the input needs to be stripped of html then it needs that regardless
>> of the output vector. again removing or not-accepting input if it contains
>> '--' is a question of filtering/validation ... besides which '--' is quite
>> acceptable for data stored in a text field but not for a numeric one.
>
> I'm not accepting "--" at all until someone can show me a real world
> case where one would use it, without the intention of SQL injection.
> How can it be escaped, anyway?

Depends on your app.

-- is an accepted things in emails as a marker for signatures.


Also in mysql_query ; is automatically handled, you can't send multiple
queries to mysql_query and have them execute.

mysql_query() sends an unique query (multiple queries are not supported)



Not sure why the php guys have only done that for mysql_query but there
you go

--
Postgresql & php tutorials
http://www.designmagick.com/

Dotan Cohen schreef:
> On 23/01/2008, Jochem Maas <jochem@iamjochem.com> wrote:
>> I can read, I saw 2 functions the first time. each function cleans *and* escapes.
>>
>> cleaning is filtering of input.
>> escaping is preparing for output.
>>
>> 2 concepts.
>
> I see your point.
>
>> if the input needs to be stripped of html then it needs that regardless
>> of the output vector. again removing or not-accepting input if it contains
>> '--' is a question of filtering/validation ... besides which '--' is quite
>> acceptable for data stored in a text field but not for a numeric one.
>
> I'm not accepting "--" at all until someone can show me a real world
> case where one would use it, without the intention of SQL injection.
> How can it be escaped, anyway?

I might just want to put '--' in a textfield used as the basis for content
for a webpage. just because I want to. the most pertinent example are wikis,
they use '--' as markup (which is usually transformed into an <hr /> when the
results are output for viewing ... but obviously you want the original markup
when editing.

INSERT INTO foo (textfield) VALUES ('--');

nothing to escape in the case of a those chars being part of a string, the escaping
mechanism [hopefully] ensures that a given string will never contain a byte sequence that
the query parser will misinterpret as a sign to end the string (before the last intend quote
delimiter) prematurely and thereby treat the remainder of the input string as SQL.

>
>> filter each piece of data
>> validate each piece of data
>> escape each peice of data for each context in which it will be output.
>
> I see that you have more experience than I!
>
>> imho your functions are conceptually wrong and not very robust either -
>> don't take it as a personal attack - I'm very sure if we sat down with *some*
>> of my code the same critism could be made to more or lesser extent :-) ...
>> "getting better all the time" as they sang once ;-)
>
> I never thought that was a personal attack, not for a second. Rather,
> I very much appreciate the time you take to explain to me my errors.
> And I intend to learn from them. For the time being, I'll leave the
> code as it is. However, for future projects, I will make a point of
> separating the different functions. Thanks.
>
> Dotan Cohen
>
> http://what-is-what.com
> http://gibberish.co.il
> א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת
>
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?

On 23/01/2008, Chris <dmagick@gmail.com> wrote:
> > I'm not accepting "--" at all until someone can show me a real world
> > case where one would use it, without the intention of SQL injection.
> > How can it be escaped, anyway?
>
> Depends on your app.
>
> -- is an accepted things in emails as a marker for signatures.

You win that one.

> Also in mysql_query ; is automatically handled, you can't send multiple
> queries to mysql_query and have them execute.
>
> mysql_query() sends an unique query (multiple queries are not supported)

Very nice to know this. Thanks.

Dotan Cohen

http://what-is-what.com
http://gibberish.co.il
א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת

A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

On 23/01/2008, Jochem Maas <jochem@iamjochem.com> wrote:
> Dotan Cohen schreef:
> > I'm not accepting "--" at all until someone can show me a real world
> > case where one would use it, without the intention of SQL injection.
> > How can it be escaped, anyway?
>
> I might just want to put '--' in a textfield used as the basis for content
> for a webpage. just because I want to. the most pertinent example are wikis,
> they use '--' as markup (which is usually transformed into an <hr /> when the
> results are output for viewing ... but obviously you want the original markup
> when editing.

Just because I want to is not a real world example. The wiki bit is.

> INSERT INTO foo (textfield) VALUES ('--');
>
> nothing to escape in the case of a those chars being part of a string, the escaping
> mechanism [hopefully] ensures that a given string will never contain a byte sequence that
> the query parser will misinterpret as a sign to end the string (before the last intend quote
> delimiter) prematurely and thereby treat the remainder of the input string as SQL.

Is the "--" here not treated as the beginning of an SQL comment?

Dotan Cohen

http://what-is-what.com
http://gibberish.co.il
א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת

A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

On Jan 22, 2008 7:01 PM, Dotan Cohen <dotancohen@gmail.com> wrote:
> I have a file of my own functions that I include in many places. One
> of them uses mysql_real_escape_string, however, it may be called in a
> context that will or will not connect to a mysql server, and worse,
> may already be connected. So I must avoid connecting. However, when I
> run the script without connecting I get this error:
>
> Warning: mysql_real_escape_string()
> [function.mysql-real-escape-string]: Access denied for user:
> 'nobody@localhost' (Using password: NO)
>
> I was thinking about checking if there is a connection, and if not
> then connecting. This seems redundant to me, however. What is the
> list's opinion of this situation? Thanks in advance.
>
> Dotan Cohen
>
> http://what-is-what.com
> http://gibberish.co.il
> א-ב-ג-ד-ה-ו-ז-ח-ט-י-ך-כ-ל-ם-מ-ן-נ-ס-ע-ף-פ-ץ-צ-ק-ר-ש-ת
>
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
>

Why not write a function that does the same thing?
mysql_real_escape_strings is a very simple function. And if your data
is properly normalized and you don't support other charsets its very
simple.