Sanitizing potential MySQL strings with no database connection

How can I configure mysql_real_escape_string() to _not_ need a
database connection in order to do it's work on a string. I understand
that the function wants a database connection to determine which
charset / encoding is in use, but in my case it will always be UTF-8.

I have a file of reusable functions that I include in several scripts,
one of them is a MySQL sanitation function, like this:
function clean_mysql ($dirty) {
$dirty=trim($dirty);
$clean=mysql_real_escape_string($dirty);
return $clean;
}

As different scripts reuse this code but connect to different
databases, I need the function to work independently of the database
connection. In other words, the include file cannot connect to the
database but it still must perform the mysql_real_escape_string()
function on UTF-8 data.

Thanks in advance for any ideas.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

----- Original Message ----
> From: Dotan Cohen
> To: php-general.
> Sent: Fri, October 16, 2009 7:13:41 PM
> Subject: [PHP] Sanitizing potential MySQL strings with no database connection
>
> How can I configure mysql_real_escape_string() to _not_ need a
> database connection in order to do it's work on a string. I understand
> that the function wants a database connection to determine which
> charset / encoding is in use, but in my case it will always be UTF-8.
>
> I have a file of reusable functions that I include in several scripts,
> one of them is a MySQL sanitation function, like this:
> function clean_mysql ($dirty) {
> $dirty=trim($dirty);
> $clean=mysql_real_escape_string($dirty);
> return $clean;
> }
>
> As different scripts reuse this code but connect to different
> databases, I need the function to work independently of the database
> connection. In other words, the include file cannot connect to the
> database but it still must perform the mysql_real_escape_string()
> function on UTF-8 data.
>
> Thanks in advance for any ideas.
>
> --
> Dotan Cohen
>
> http://what-is-what.com
> http://gibberish.co.il
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

Dotan,

I don't think so since the mysql_real_escape_string() requires a connection handler. Why not use bind param?

Regards,
Tommy


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> I don't think so since the mysql_real_escape_string() requires a connecti=
on handler.  Why not use bind param?
>

Thanks. I just googled bind param but I am still a bit unclear as to
what is going on.

To be clear, I have a file of functions that I use in many scripts,
lets call it functions.inc. One of the functions calls
mysql_real_escape_string() but in order to do that it looks like I
have to connect to a database. However, different scripts connect to
different databases, and some do not connect to a database at all, so
I cannot simple connect to a database from the functions.inc file as
that will interfere with the database connections going on in the
scripts including that file.

--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

----- Original Message ----
> From: Dotan Cohen
> To: Tommy Pham
> Cc: php-general.
> Sent: Sat, October 17, 2009 10:59:52 AM
> Subject: Re: [PHP] Sanitizing potential MySQL strings with no database connection
>
> > I don't think so since the mysql_real_escape_string() requires a connection
> handler. Why not use bind param?
> >
>
> Thanks. I just googled bind param but I am still a bit unclear as to
> what is going on.
>
> To be clear, I have a file of functions that I use in many scripts,
> lets call it functions.inc. One of the functions calls
> mysql_real_escape_string() but in order to do that it looks like I
> have to connect to a database. However, different scripts connect to
> different databases, and some do not connect to a database at all, so
> I cannot simple connect to a database from the functions.inc file as
> that will interfere with the database connections going on in the
> scripts including that file.
>
> --
> Dotan Cohen
>
> http://what-is-what.com
> http://gibberish.co.il
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

I assumed the reason you wanted to do escape the string so that you could perform DB operations. In your select/insert/update class(es)/function(s), you could just use prepare statement and bind param. Thus, no need to escape the string to protect against injection. It's also faster if by chance you're doing several updates/inserts due to the nature of prepare statement. You could use a call back function in case you have a varying size array of parameters, making your code more adaptable and somewhat smaller. I generally prefer using prepare statement + bind param over escape string + query for speed and flexibility.

http://www.php.net/manual/en/mysqli.prepare.php
http://www.php.net/manual/en/mysqli-stmt.bind-param.php

have good examples.

Regards,
Tommy


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote:
> How can I configure mysql_real_escape_string() to _not_ need a
> database connection in order to do it's work on a string. I understand
> that the function wants a database connection to determine which
> charset / encoding is in use, but in my case it will always be UTF-8.
>
> I have a file of reusable functions that I include in several scripts,
> one of them is a MySQL sanitation function, like this:
> function clean_mysql ($dirty) {
> $dirty=trim($dirty);
> $clean=mysql_real_escape_string($dirty);
> return $clean;
> }
>
> As different scripts reuse this code but connect to different
> databases, I need the function to work independently of the database
> connection. In other words, the include file cannot connect to the
> database but it still must perform the mysql_real_escape_string()
> function on UTF-8 data.
>
> Thanks in advance for any ideas.
>

What is your intension when calling this function, if you are not connecting to a DB? I realize you
want to sanitize a string, but why? The only reason to use mysql_real_escape_string() would be to
sanitize a string to prepare it to be used in a query against a mysql database.

If you are simply looking to escape a (UTF-8) string, why not just use the other built in escape
functions from PHP?

What does mysql_real_escape_string() offer you that addslashes(), addcslashes(), htmlentities(),
quotemeta(), htmlspecialchars(), etc... would not offer you?

What type of data are you trying to protect yourself from? And what are you planning on doing with
the output?

--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> I assumed the reason you wanted to do escape the string so that you could=
perform DB operations.

Yes, that is my intention. However, the function is found in an
include file of functions used in many different scripts, each of
which connect to a different database or may not connect to a database
at all, so I cannot rely on there existing a database connection. The
workaround would be to include this particular function in a separate
include file to only be included when a database connection is
present, but I would like to find a better way as I find it most
maintainable to have all my reused functions in a single file.

To give you an idea, the file contains these funtions:
function clean_mysql ($dirty)
function clean_html ($dirty)
function make_paginated_links_menu ($pages, $difference)
function obfuscate_email_address ($address)

Not all functions are used in all pages, however, this file of
reusable functions is included in all of them. Only the clean_mysql
function gives me trouble because I cannot ensure a database
connection.

> In your select/insert/update class(es)/function(s), you could just u=
se prepare statement and bind param.  Thus, no need
> to escape the string to protect against injection.  It's also faster=
if by chance you're doing several updates/inserts due
> to the nature of prepare statement.  You could use a call back funct=
ion in case you have a varying size array of
> parameters, making your code more adaptable and somewhat smaller.  I=
generally prefer using prepare statement +
> bind param over escape string + query for speed and flexibility.
>
> http://www.php.net/manual/en/mysqli.prepare.php
> http://www.php.net/manual/en/mysqli-stmt.bind-param.php
>
> have good examples.
>

Thanks. Going through those pages, I see that it is not what I need.
It is good to know, though.


--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote on 2009-10-18 10:52:
>> I assumed the reason you wanted to do escape the string so that you could perform DB operations.
>
> Yes, that is my intention. However, the function is found in an
> include file of functions used in many different scripts, each of
> which connect to a different database or may not connect to a database
> at all, so I cannot rely on there existing a database connection.


test if you have a db connection in the function, if not, skip MRES and
other mysql_ functions?

In my opinion it's bad code to use a mysql_* function on a Oracle db
(and vice versa) or on a string for that matter. It lies in the naming
of the function what it's designed to do and work on. If you want a
general function to sanitize an input, make your own function
sanitize_input() based on ereg_* and/or str_replace and the likes.

--
Kind regards
Kim Emax

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> test if you have a db connection in the function, if not, skip MRES and
> other mysql_ functions?
>

I thought that one could not test if a database connection is
established or not, this is the most relevant thing that I found while
googling that:
http://bugs.php.net/bug.php?id=29645

> In my opinion it's bad code to use a mysql_* function on a Oracle db (and
> vice versa) or on a string for that matter. It lies in the naming of the
> function what it's designed to do and work on. If you want a general
> function to sanitize an input, make your own function sanitize_input() based
> on ereg_* and/or str_replace and the likes.
>

All the connections are to MySQL databases, but to _different_ MySQL
databases on the same host.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote on 2009-10-18 21:21:

> I thought that one could not test if a database connection is
> established or not, this is the most relevant thing that I found while
> googling that:
> http://bugs.php.net/bug.php?id=29645

from http://www.php.net/manual/en/function.mysql-connect.php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}

So just test if $link is available

> All the connections are to MySQL databases, but to _different_ MySQL
> databases on the same host.

Would't this solve you problem?

$link1 = mysql_connect('localhost', 'mysql_user1', 'mysql_password');
$link2 = mysql_connect('localhost', 'mysql_user2', 'mysql_password');

if($link1) {
etc...

or I would say that your "different scripts" should require different db
connection files.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

2009/10/19 Kim Madsen :
> Dotan Cohen wrote on 2009-10-18 21:21:
>
>> I thought that one could not test if a database connection is
>> established or not, this is the most relevant thing that I found while
>> googling that:
>> http://bugs.php.net/bug.php?id=3D29645
>
> from http://www.php.net/manual/en/function.mysql-connect.php
>
> $link =3D mysql_connect('localhost', 'mysql_user', 'mysql_password');
> if (!$link) {
>    die('Could not connect: ' . mysql_error());
> }
>
> So just test if $link is available
>

I need to know if there is _any_ connection available, not a specific
connection. In one script it may be $link but in another $connection.


>> All the connections are to MySQL databases, but to _different_ MySQL
>> databases on the same host.
>
> Would't this solve you problem?
>
> $link1 =3D mysql_connect('localhost', 'mysql_user1', 'mysql_password');
> $link2 =3D mysql_connect('localhost', 'mysql_user2', 'mysql_password');
>
> if($link1) {
> etc...
>
> or I would say that your "different scripts" should require different db
> connection files.
>

Of course they connect differently, each to a different database (all
on localhost).


--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote:
> 2009/10/19 Kim Madsen :
>> Dotan Cohen wrote on 2009-10-18 21:21:
>>
>>> I thought that one could not test if a database connection is
>>> established or not, this is the most relevant thing that I found while
>>> googling that:
>>> http://bugs.php.net/bug.php?id=29645
>> from http://www.php.net/manual/en/function.mysql-connect.php
>>
>> $link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
>> if (!$link) {
>> die('Could not connect: ' . mysql_error());
>> }
>>
>> So just test if $link is available
>>
>
> I need to know if there is _any_ connection available, not a specific
> connection. In one script it may be $link but in another $connection.
>

Dotan,

You are making this thing harder then it has to be.

All you need is to replicate the escaping of the same characters that
mysql_real_escape_string() escapes. Simply do that. They are listed on the
functions manual page on php.net

http://php.net/mysql_real_escape_string

Here is a function that I mocked up really quick.

I have no idea if it will work, but it is a start down the right road to solve
your problem(s)...


function clean_string($input) {

/**
* Character to escape...
* \x0 \n \r \ ' " \x1a
**/

$patterns = array( "\x0", "\n", "\r", "\\", "'", "\"", "\x1a");
$replace = array( '\\\x0', '\n', '\r', '\\\\', '\\\'', '\\"', '\\\x1a');
return str_replace($patterns, $replace, $input);
}

?>

Jim Lucas

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> Dotan,
>
> You are making this thing harder then it has to be.
>
> All you need is to replicate the escaping of the same characters that
> mysql_real_escape_string() escapes.  Simply do that.  They are =
listed on the
> functions manual page on php.net
>
> http://php.net/mysql_real_escape_string
>
> Here is a function that I mocked up really quick.
>
> I have no idea if it will work, but it is a start down the right road to =
solve
> your problem(s)...
>
> >
> function clean_string($input) {
>
>  /**
>   * Character to escape...
>   *    \x0     \n      \r   =
   \       '       "     =
  \x1a
>  **/
>
>  $patterns =3D array( "\x0",   "\n", "\r", "\\",   "'", =
   "\"", "\x1a");
>  $replace =3D array(  '\\\x0', '\n', '\r', '\\\\', '\\\'', '\\"=
',  '\\\x1a');
>  return str_replace($patterns, $replace, $input);
> }
>
> ?>
>

I think that I would rather trust the built-in functions. I don't need
to do anything "smart" and get attacked. Anybody else have an opinion
on this?


--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

--=-d+R0S+ENvKgdju7Ghcem
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2009-10-20 at 12:58 +0200, Dotan Cohen wrote:

> > Dotan,
> >
> > You are making this thing harder then it has to be.
> >
> > All you need is to replicate the escaping of the same characters that
> > mysql_real_escape_string() escapes. Simply do that. They are listed on the
> > functions manual page on php.net
> >
> > http://php.net/mysql_real_escape_string
> >
> > Here is a function that I mocked up really quick.
> >
> > I have no idea if it will work, but it is a start down the right road to solve
> > your problem(s)...
> >
> > > >
> > function clean_string($input) {
> >
> > /**
> > * Character to escape...
> > * \x0 \n \r \ ' " \x1a
> > **/
> >
> > $patterns = array( "\x0", "\n", "\r", "\\", "'", "\"", "\x1a");
> > $replace = array( '\\\x0', '\n', '\r', '\\\\', '\\\'', '\\"', '\\\x1a');
> > return str_replace($patterns, $replace, $input);
> > }
> >
> > ?>
> >
>
> I think that I would rather trust the built-in functions. I don't need
> to do anything "smart" and get attacked. Anybody else have an opinion
> on this?
>
>
> --
> Dotan Cohen
>
> http://what-is-what.com
> http://gibberish.co.il
>


Your only option might be to do something "smart". You can't use the
proper mysql functions without a connection to a database, but you
refuse to connect to a database until after you perform validation...

You do realise you can have several db connections open at one time, so
you could have one always open for the purpose of validation?
Potentially wasteful, but the architecture in this idea is a little
different from the norm.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-d+R0S+ENvKgdju7Ghcem--

RE: Sanitizing potential MySQL strings with no database connection

--_7ec788c8-1f67-40a4-8d8e-43f73ff4c1be_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



> Your only option might be to do something "smart". You can't use the
> proper mysql functions without a connection to a database=2C but you
> refuse to connect to a database until after you perform validation...
>=20
> You do realise you can have several db connections open at one time=2C so
> you could have one always open for the purpose of validation?
> Potentially wasteful=2C but the architecture in this idea is a little
> different from the norm.

I also thought mysql_real_escape_string was dead since every DAL such PDO o=
r others uses bindings to properly escape variables and a database related =
sanitize without database is quite useless=2C imho.

Regards
=20
____________________________________________________________ _____
Windows Live: Friends get your Flickr=2C Yelp=2C and Digg updates when they=
e-mail you.
http://www.microsoft.com/middleeast/windows/windowslive/see- it-in-action/so=
cial-network-basics.aspx?ocid=3DPID23461::T:WLMTAGL:ON:WL:en -xm:SI_SB_3:092=
010=

--_7ec788c8-1f67-40a4-8d8e-43f73ff4c1be_--

RE: Sanitizing potential MySQL strings with no databaseconnection

--=-ORFOs9bdcJvVtmTWqjqn
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2009-10-20 at 14:20 +0200, Andrea Giammarchi wrote:

>
> > Your only option might be to do something "smart". You can't use the
> > proper mysql functions without a connection to a database, but you
> > refuse to connect to a database until after you perform validation...
> >
> > You do realise you can have several db connections open at one time, so
> > you could have one always open for the purpose of validation?
> > Potentially wasteful, but the architecture in this idea is a little
> > different from the norm.
>
> I also thought mysql_real_escape_string was dead since every DAL such PDO or others uses bindings to properly escape variables and a database related sanitize without database is quite useless, imho.
>
> Regards
>
> ____________________________________________________________ _____
> Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you.
> http://www.microsoft.com/middleeast/windows/windowslive/see- it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLM TAGL:ON:WL:en-xm:SI_SB_3:092010


Not everyone uses something like PDO, so yes, sanitising data with
mysql_real_escape_string does still happen.

The function clearly states that it needs an open connection to work, so
that leaves two choices really: 1) open a damn connection! or 2)
reinvent the wheel and create a function which mimics the behavior of
this one.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-ORFOs9bdcJvVtmTWqjqn--

RE: Sanitizing potential MySQL strings with no database connection

From: Ashley Sheridan

> On Tue, 2009-10-20 at 14:20 +0200, Andrea Giammarchi wrote:
>> > Your only option might be to do something "smart". You can't use
the
>> > proper mysql functions without a connection to a database, but you
>> > refuse to connect to a database until after you perform
validation...
>> >=20
>> > You do realise you can have several db connections open at one
time, so
>> > you could have one always open for the purpose of validation?
>> > Potentially wasteful, but the architecture in this idea is a little
>> > different from the norm.
>>=20
>> I also thought mysql_real_escape_string was dead since every DAL such
>> PDO or others uses bindings to properly escape variables and a
database
>> related sanitize without database is quite useless, imho.
>>=20
>=20
> Not everyone uses something like PDO, so yes, sanitising data with
> mysql_real_escape_string does still happen.
>=20
> The function clearly states that it needs an open connection to work,
so
> that leaves two choices really: 1) open a damn connection! or 2)
> reinvent the wheel and create a function which mimics the behavior of
> this one.

Is the database connection used to determine the character encoding to
be used before it inserts new characters into the strings? Would that
make a difference in this case?

Bob McConnell

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Sanitizing potential MySQL strings with no databaseconnection

--=-IffzOau9zop1iJRTlYQB
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2009-10-20 at 08:43 -0400, Bob McConnell wrote:

> From: Ashley Sheridan
>
> > On Tue, 2009-10-20 at 14:20 +0200, Andrea Giammarchi wrote:
> >> > Your only option might be to do something "smart". You can't use
> the
> >> > proper mysql functions without a connection to a database, but you
> >> > refuse to connect to a database until after you perform
> validation...
> >> >
> >> > You do realise you can have several db connections open at one
> time, so
> >> > you could have one always open for the purpose of validation?
> >> > Potentially wasteful, but the architecture in this idea is a little
> >> > different from the norm.
> >>
> >> I also thought mysql_real_escape_string was dead since every DAL such
> >> PDO or others uses bindings to properly escape variables and a
> database
> >> related sanitize without database is quite useless, imho.
> >>
> >
> > Not everyone uses something like PDO, so yes, sanitising data with
> > mysql_real_escape_string does still happen.
> >
> > The function clearly states that it needs an open connection to work,
> so
> > that leaves two choices really: 1) open a damn connection! or 2)
> > reinvent the wheel and create a function which mimics the behavior of
> > this one.
>
> Is the database connection used to determine the character encoding to
> be used before it inserts new characters into the strings? Would that
> make a difference in this case?
>
> Bob McConnell
>


Yes, the mysql_real_escape_string() function uses the databases
character encoding to determine how to encode the string, whereas the
older deprecated version mysql_escape_string() required no connection as
it always assumed Latin-1 (as far as I know) The data itself only needs
to be sanitised just prior to being inserted into the DB anyway, it
shouldn't be used to validate data in any way, there are functions
specifically for that. To me, it just seems that the logic of the script
is flawed if you require the data to be sanitised before a connection
has been made to the DB.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-IffzOau9zop1iJRTlYQB--

Re: Sanitizing potential MySQL strings with no database

> Your only option might be to do something "smart". You can't use the proper mysql functions without a connection to a
> database, but you refuse to connect to a database until after you perform validation...
>

More accurate to say that the file in which the function is stored
does not know if there is a connection or not. I would make such a
connection if I knew that none exist, but I do not want to interfere
with a possibly existing connection.


> You do realise you can have several db connections open at one time, so you could have one always open for the
> purpose of validation? Potentially wasteful, but the architecture in this idea is a little different from the norm.
>

Very wasteful indeed, I cannot be so irresponsible with this server.


--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> Yes, the mysql_real_escape_string() function uses the databases character encoding to determine how to encode the
> string, whereas the older deprecated version mysql_escape_string() required no connection as it always assumed
> Latin-1 (as far as I know)

Is there such a function that always assumes UTF-8? That's what it
always will be.


> The data itself only needs to be sanitised just prior to being inserted into the DB anyway, it
> shouldn't be used to validate data in any way, there are functions specifically for that. To me, it just seems that the logic
> of the script is flawed if you require the data to be sanitised before a connection has been made to the DB.
>

I am not requiring the data to be sanitised before a connection has
been made to the DB. The function that calls
mysql_real_escape_string() is in an include file of commonly-reused
functions. Scripts that connect to databases and scripts that do not
connect to databases include this file.

To clarify, the include file contains these funtions:
function clean_mysql ($dirty)
function clean_html ($dirty)
function make_paginated_links_menu ($pages, $difference)
function obfuscate_email_address ($address)

Not all of the functions are used in all scripts, however, this file
of reusable functions is included in all of them. Only the clean_mysql
function gives me trouble because it calls mysql_real_escape_string().

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

--=-hvb9U1XADHWriMupuhVk
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2009-10-20 at 14:58 +0200, Dotan Cohen wrote:

> > Yes, the mysql_real_escape_string() function uses the databases character encoding to determine how to encode the
> > string, whereas the older deprecated version mysql_escape_string() required no connection as it always assumed
> > Latin-1 (as far as I know)
>
> Is there such a function that always assumes UTF-8? That's what it
> always will be.
>
>
> > The data itself only needs to be sanitised just prior to being inserted into the DB anyway, it
> > shouldn't be used to validate data in any way, there are functions specifically for that. To me, it just seems that the logic
> > of the script is flawed if you require the data to be sanitised before a connection has been made to the DB.
> >
>
> I am not requiring the data to be sanitised before a connection has
> been made to the DB. The function that calls
> mysql_real_escape_string() is in an include file of commonly-reused
> functions. Scripts that connect to databases and scripts that do not
> connect to databases include this file.
>
> To clarify, the include file contains these funtions:
> function clean_mysql ($dirty)
> function clean_html ($dirty)
> function make_paginated_links_menu ($pages, $difference)
> function obfuscate_email_address ($address)
>
> Not all of the functions are used in all scripts, however, this file
> of reusable functions is included in all of them. Only the clean_mysql
> function gives me trouble because it calls mysql_real_escape_string().
>
> --
> Dotan Cohen
>
> http://what-is-what.com
> http://gibberish.co.il
>


No, and you clearly missed the point about that function being pretty
much dead anyway.

You mentioned also in your last email that you would make a DB
connection if none existed. That should be very easy if you read the
page on mysql_real_escape_string()

If says:

Returns the escaped string, or FALSE on error.

So all you have to do, is have warnings turned off (as it generates an
E_WARNING if you have no active connection) and then look at the return
value of a call to the function:

if(mysql_real_escape_string($variable) === false)
{
// create a default DB connection
}

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-hvb9U1XADHWriMupuhVk--

RE: Sanitizing potential MySQL strings with no database connection

--_b8b05960-7a95-4f73-9f11-97e270742df0_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


> If says:
>=20
> Returns the escaped string=2C or FALSE on error.
>=20
> So all you have to do=2C is have warnings turned off (as it generates an
> E_WARNING if you have no active connection) and then look at the return
> value of a call to the function:
>=20
> if(mysql_real_escape_string($variable) ===3D false)
> {
> // create a default DB connection
> }

I would rather suggest:

$error_reporting =3D error_reporting(0)=3B
if(mysql_real_escape_string($variable) ===3D false)
{
// create a default DB connection
}
error_reporting($error_reporting)=3B
unset($error_reporting)=3B

=20
____________________________________________________________ _____
Windows Live: Keep your friends up to date with what you do online.
http://www.microsoft.com/middleeast/windows/windowslive/see- it-in-action/so=
cial-network-basics.aspx?ocid=3DPID23461::T:WLMTAGL:ON:WL:en -xm:SI_SB_1:092=
010=

--_b8b05960-7a95-4f73-9f11-97e270742df0_--

RE: Sanitizing potential MySQL strings with no database connection

--_3d5c1e5b-8b2e-4100-af6f-d326f8179a00_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


even better

$error_reporting =3D error_reporting(0)=3B
if(mysql_real_escape_string($variable) ===3D false)
{
error_reporting($error_reporting)=3B

// create a default DB connection

} else
error_reporting($error_reporting)=3B
unset($error_reporting)=3B

> From: an_red@hotmail.com
> To: ash@ashleysheridan.co.uk=3B dotancohen@gmail.com
> CC: php-general@lists.php.net
> Date: Tue=2C 20 Oct 2009 15:50:52 +0200
> Subject: RE: [PHP] Sanitizing potential MySQL strings with no database c=
onnection
>=20
>=20
> > If says:
> >=20
> > Returns the escaped string=2C or FALSE on error.
> >=20
> > So all you have to do=2C is have warnings turned off (as it generates a=
n
> > E_WARNING if you have no active connection) and then look at the return
> > value of a call to the function:
> >=20
> > if(mysql_real_escape_string($variable) ===3D false)
> > {
> > // create a default DB connection
> > }
>=20
> I would rather suggest:
>=20
> $error_reporting =3D error_reporting(0)=3B
> if(mysql_real_escape_string($variable) ===3D false)
> {
> // create a default DB connection
> }
> error_reporting($error_reporting)=3B
> unset($error_reporting)=3B
>=20
> =20
> ____________________________________________________________ _____
> Windows Live: Keep your friends up to date with what you do online.
> http://www.microsoft.com/middleeast/windows/windowslive/see- it-in-action/=
social-network-basics.aspx?ocid=3DPID23461::T:WLMTAGL:ON:WL: en-xm:SI_SB_1:0=
92010
=20
____________________________________________________________ _____
Windows Live: Keep your friends up to date with what you do online.
http://www.microsoft.com/middleeast/windows/windowslive/see- it-in-action/so=
cial-network-basics.aspx?ocid=3DPID23461::T:WLMTAGL:ON:WL:en -xm:SI_SB_1:092=
010=

--_3d5c1e5b-8b2e-4100-af6f-d326f8179a00_--

Re: Sanitizing potential MySQL strings with no database connection

On Tue, 20 Oct 2009 14:58:32 +0200, Dotan Cohen wrote:

>> Yes, the mysql_real_escape_string() function uses the databases
>> character encoding to determine how to encode the string, whereas the
>> older deprecated version mysql_escape_string() required no connection
>> as it always assumed Latin-1 (as far as I know)
>
> Is there such a function that always assumes UTF-8? That's what it
> always will be.

If you're sure that all your data is UTF-8, and that
all user-supplied data is *actually valid* UTF-8 (and
not deliberately or accidentally malformed), then
mysql_escape_string() should be just fine [1].

It should be fine for any character set that leave
ASCII characters unchanged and do not contain any
characters that could (partially) be mistaken for one of
the "dangerous" ASCII characters.

Of course, mysql_escape_string() is deprecated and will
be removed in PHP6 [2], in which case you could fix all
the bugs in the hand-rolled function posted earlier and
use that.

> I am not requiring the data to be sanitised before a connection has been
> made to the DB. The function that calls mysql_real_escape_string() is in
> an include file of commonly-reused functions. Scripts that connect to
> databases and scripts that do not connect to databases include this
> file.
>
> To clarify, the include file contains these funtions: function
> clean_mysql ($dirty) function clean_html ($dirty) function
> make_paginated_links_menu ($pages, $difference) function
> obfuscate_email_address ($address)

*Or*, you could do the *obvious* thing [3]:

function clean_mysql ($dirty, $connection) { ... }


But then, you'll end up having to rewrite a lot of
function calls [4].


/Nisse


[1]: Unless it's not.
[2]: Unless it isn't.
[3]: Well, one of them anyway.
[4]: Unless you don't.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

On Mon, 19 Oct 2009 15:39:40 -0700, Jim Lucas wrote:

> I have no idea if it will work, [...]

Well, you're right so far...

> >
> function clean_string($input) {
>
> /**
> * Character to escape...
> * \x0 \n \r \ ' " \x1a
> **/
>
> $patterns = array( "\x0", "\n", "\r", "\\", "'", "\"", "\x1a");
> $replace = array( '\\\x0', '\n', '\r', '\\\\', '\\\'', '\\"', '\\\x1a');
> return str_replace($patterns, $replace, $input);
> }

Not only does this not do quite what mysql_escape_string()
does, but it also fails to not do so spectacularly.

Hint:

echo str_replace (array('a','b'), array('b','c'), 'a'), "\n";


/Nisse

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> No, and you clearly missed the point about that function being pretty muc=
h dead anyway.
>

I understand that mysql_escape_string() is depreciated. Asking about
other similar functions does not seem out of line.


> You mentioned also in your last email that you would make a DB connection=
if none existed. That should be very easy
> if you read the page on mysql_real_escape_string()
>
> If says:
>
> Returns the escaped string, or FALSE on error.
>
> So all you have to do, is have warnings turned off (as it generates an E_=
WARNING if you have no active connection) and then look at the return value=
of a call to the function:
>
> if(mysql_real_escape_string($variable) ===3D false)
> {
>     // create a default DB connection
> }
>

Here, the key seems to be to turn the warning level down, which I do
not have privileges to do on this server. But it fact this seems to be
the key that I was missing, and even though I cannot make use of it at
least I know in general what needs to be done.

Thanks.


--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

2009/10/20 Andrea Giammarchi :
> even better
>
> $error_reporting =3D error_reporting(0);
> if(mysql_real_escape_string($variable) ===3D false)
> {
>     error_reporting($error_reporting);
>
>     // create a default DB connection
>
> } else
>     error_reporting($error_reporting);
> unset($error_reporting);
>

Thanks, I will try that this evening. I may not have permissions for
that, but we'll see.

--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote on 2009-10-20 20:06:

>> if(mysql_real_escape_string($variable) === false)
>> {
>> // create a default DB connection
>> }
>>
>
> Here, the key seems to be to turn the warning level down, which I do
> not have privileges to do on this server. But it fact this seems to be
> the key that I was missing, and even though I cannot make use of it at
> least I know in general what needs to be done.

if(@mysql_real_escape_string($variable) === false)

Well?

--
Kind regards
Kim Emax - masterminds.dk

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

>   If you're sure that all your data is UTF-8, and that
> all user-supplied data is *actually valid* UTF-8 (and
> not deliberately or accidentally malformed), then
> mysql_escape_string() should be just fine [1].
>

I cannot ensure that the users will not be malicious, even if it is
all internal users.


--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote:
> 2009/10/20 Andrea Giammarchi :
>> even better
>>
>> $error_reporting = error_reporting(0);
>> if(mysql_real_escape_string($variable) === false)
>> {
>> error_reporting($error_reporting);
>>
>> // create a default DB connection
>>
>> } else
>> error_reporting($error_reporting);
>> unset($error_reporting);
>>
>
> Thanks, I will try that this evening. I may not have permissions for
> that, but we'll see.
>

I stole this from ZF:

function dotan_real_escape_string($value)
{
if (is_int($value)) {
return $value;
} elseif (is_float($value)) {
return sprintf('%F', $value);
}
return "'" . addcslashes($value, "\000\n\r\\'\"\032") . "'";
}

--
Thanks!
-Shawn
http://www.spidean.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> if(@mysql_real_escape_string($variable) === false)
>

Perfect! The @ symbol suppresses the error and I can structure the
code according to whether or not there is a connection.

Thank you!

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

On Tue, 20 Oct 2009 20:04:51 +0200, Nisse Engström wrote:

> On Mon, 19 Oct 2009 15:39:40 -0700, Jim Lucas wrote:
>
>> /**
>> * Character to escape...
>> * \x0 \n \r \ ' " \x1a
>> **/
>>
>> $patterns = array( "\x0", "\n", "\r", "\\", "'", "\"", "\x1a");
>> $replace = array( '\\\x0', '\n', '\r', '\\\\', '\\\'', '\\"', '\\\x1a');
>> return str_replace($patterns, $replace, $input);
>> }
>
> Not only does this not do quite what mysql_escape_string()

Brain fart. I was looking at the wrong list of characters
that should be escaped.

> does, but it also fails to not do so spectacularly.

Still...


/Nisse

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Jim Lucas wrote:
> Dotan Cohen wrote:
>> 2009/10/19 Kim Madsen :
>>> Dotan Cohen wrote on 2009-10-18 21:21:
>>>
>>>> I thought that one could not test if a database connection is
>>>> established or not, this is the most relevant thing that I found while
>>>> googling that:
>>>> http://bugs.php.net/bug.php?id=29645
>>> from http://www.php.net/manual/en/function.mysql-connect.php
>>>
>>> $link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
>>> if (!$link) {
>>> die('Could not connect: ' . mysql_error());
>>> }
>>>
>>> So just test if $link is available
>>>
>> I need to know if there is _any_ connection available, not a specific
>> connection. In one script it may be $link but in another $connection.
>>
>
> Dotan,
>
> You are making this thing harder then it has to be.
>
> All you need is to replicate the escaping of the same characters that
> mysql_real_escape_string() escapes. Simply do that. They are listed on the
> functions manual page on php.net
>
> http://php.net/mysql_real_escape_string
>
> Here is a function that I mocked up really quick.
>
> I have no idea if it will work, but it is a start down the right road to solve
> your problem(s)...
>
> >
> function clean_string($input) {
>
> /**
> * Character to escape...
> * \x0 \n \r \ ' " \x1a
> **/
>
> $patterns = array( "\x0", "\n", "\r", "\\", "'", "\"", "\x1a");
> $replace = array( '\\\x0', '\n', '\r', '\\\\', '\\\'', '\\"', '\\\x1a');
> return str_replace($patterns, $replace, $input);
> }
>
> ?>
>
> Jim Lucas
>

So, actually taking a minute to read up on addcslashes(), it is a rather handy
little function.

Taking the list of characters that mysql_real_escape_string() says it escapes:

http://us3.php.net/mysql_real_escape_string

Which it lists: \x00, \n, \r, \, ', " and \x1a

\0 = \x0
\10 = \n
\13 = \r
\92 = \
\44 = '
\34 = "
\26 = \x1a

You could do something like this.

function cleaner($input) {
return addcslashes($input, "\0\10\13\92\44\34\26");
}

Maybe this will help...

Jim

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

----- Original Message -----
From: "Ashley Sheridan"
To: "Dotan Cohen"
Cc: "Jim Lucas" ; "php-general."

Sent: Tuesday, October 20, 2009 4:02 AM
Subject: Re: [PHP] Sanitizing potential MySQL strings with no database
connection


> On Tue, 2009-10-20 at 12:58 +0200, Dotan Cohen wrote:
>
>> > Dotan,
>> >
>> > You are making this thing harder then it has to be.
>> >
>> > All you need is to replicate the escaping of the same characters that
>> > mysql_real_escape_string() escapes. Simply do that. They are listed
>> > on the
>> > functions manual page on php.net
>> >
>> > http://php.net/mysql_real_escape_string
>> >


This thread is so long, I am suprised to see that nobody has yet recommended
the use of the OWASP php filters.
It is still very good.

http://www.owasp.org/index.php/OWASP_PHP_Filters

If by chance someone already mentioned it, my bad.

Best Regards


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> So, actually taking a minute to read up on addcslashes(), it is a rather =
handy
> little function.
>
> Taking the list of characters that mysql_real_escape_string() says it esc=
apes:
>
> http://us3.php.net/mysql_real_escape_string
>
> Which it lists: \x00, \n, \r, \, ', " and \x1a
>
> \0  =3D \x0
> \10 =3D \n
> \13 =3D \r
> \92 =3D \
> \44 =3D '
> \34 =3D "
> \26 =3D \x1a
>
> You could do something like this.
>
> function cleaner($input) {
>        return addcslashes($input, "\0\10\13\92\44\34\=
26");
> }
>
> Maybe this will help...
>
> Jim
>

So far as I understand mysql_real_escape_string() was invented because
addslashes() is not adequate.


--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote:
> So far as I understand mysql_real_escape_string() was invented because
> addslashes() is not adequate.

Correct, addslashes() works fine for latin1 (single byte encoding) but
does not work properly when used with a multibyte encoded string.
That is most likely the reason why mysql_real_escape_string() checks the
encoding before escaping so it can do the right thing for the used encoding.

Here is a quote from the description of a forum SQL injection exploit:
"Addslashes simply adds a backslash (0x5c) before single quote ('),
double quote ("), backslash (\) and NUL (the NULL byte), without
checking if the added blackslash creates another char.

Bytes in Input 0xa327
Addslashes(Bytes in Input) 0xa35c27

In big5, but also in other multibyte charsets, 0xa35c is a valid char:
0x27 (') is left alone."

--
John
No Victim, No Crime

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Sanitizing potential MySQL strings with no database connection

--_282ef982-9c72-43b7-8750-941a5723a11c_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


I so much avoid the silent char that sometimes I even forget this exists.

I guess it is worth it for this case.

Regards

> Date: Tue=2C 20 Oct 2009 21:28:06 +0200
> From: dotancohen@gmail.com
> To: php.net@emax.dk
> CC: ash@ashleysheridan.co.uk=3B php-general@lists.php.net
> Subject: Re: [PHP] Sanitizing potential MySQL strings with no database c=
onnection
>=20
> > if(@mysql_real_escape_string($variable) ===3D false)
> >
>=20
> Perfect! The @ symbol suppresses the error and I can structure the
> code according to whether or not there is a connection.
>=20
> Thank you!
>=20
> --=20
> Dotan Cohen
>=20
> http://what-is-what.com
> http://gibberish.co.il
>=20
> --=20
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe=2C visit: http://www.php.net/unsub.php
>=20
=20
____________________________________________________________ _____
Keep your friends updated=97even when you=92re not signed in.
http://www.microsoft.com/middleeast/windows/windowslive/see- it-in-action/so=
cial-network-basics.aspx?ocid=3DPID23461::T:WLMTAGL:ON:WL:en -xm:SI_SB_5:092=
010=

--_282ef982-9c72-43b7-8750-941a5723a11c_--

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote:
>> So, actually taking a minute to read up on addcslashes(), it is a rather handy
>> little function.
>>
>> Taking the list of characters that mysql_real_escape_string() says it escapes:
>>
>> http://us3.php.net/mysql_real_escape_string
>>
>> Which it lists: \x00, \n, \r, \, ', " and \x1a
>>
>> \0 = \x0
>> \10 = \n
>> \13 = \r
>> \92 = \
>> \44 = '
>> \34 = "
>> \26 = \x1a
>>
>> You could do something like this.
>>
>> function cleaner($input) {
>> return addcslashes($input, "\0\10\13\92\44\34\26");
>> }
>>
>> Maybe this will help...
>>
>> Jim
>>
>
> So far as I understand mysql_real_escape_string() was invented because
> addslashes() is not adequate.
>
>

If you look a little closer, you will see that I am not using addslashes(). Rather, I am using
addcslashes(). This allows to specify the characters that I want escaped, instead of the default
assumed characters from addslashes().

--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Jim Lucas wrote:
> Dotan Cohen wrote:
>>> So, actually taking a minute to read up on addcslashes(), it is a
>>> rather handy
>>> little function.
>>>
>>> Taking the list of characters that mysql_real_escape_string() says it
>>> escapes:
>>>
>>> http://us3.php.net/mysql_real_escape_string
>>>
>>> Which it lists: \x00, \n, \r, \, ', " and \x1a
>>>
>>> \0 = \x0
>>> \10 = \n
>>> \13 = \r
>>> \92 = \
>>> \44 = '
>>> \34 = "
>>> \26 = \x1a
>>>
>>> You could do something like this.
>>>
>>> function cleaner($input) {
>>> return addcslashes($input, "\0\10\13\92\44\34\26");
>>> }
>>>
>>> Maybe this will help...
>>>
>>> Jim
>>>
>>
>> So far as I understand mysql_real_escape_string() was invented because
>> addslashes() is not adequate.
>>
>>
>
> If you look a little closer, you will see that I am not using
> addslashes(). Rather, I am using addcslashes(). This allows to specify
> the characters that I want escaped, instead of the default assumed
> characters from addslashes().
>

Thinking a little deeper here, you say you are concerned about the character
type, yet you say that it is all assumed UTF-8. Is everything going to be UTF-8
or something else?

If it is all going to be UTF-8, then the addcslashes() variation above will work.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> If you look a little closer, you will see that I am not using addslashes(=
).
>  Rather, I am using addcslashes().  This allows to specify the =
characters
> that I want escaped, instead of the default assumed characters from
> addslashes().
>

I do not know which characters to escape.

--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database

> Thinking a little deeper here, you say you are concerned about the charac=
ter
> type, yet you say that it is all assumed UTF-8.  Is everything going=
to be UTF-8
> or something else?
>
> If it is all going to be UTF-8, then the addcslashes() variation above wi=
ll work.
>

It _should_ all be UTF-8 but I suppose that it is possible for someone
to spoof a non-UTF-8 POST request. I do not want to take the
development of a secure function into my own hands.

--=20
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sanitizing potential MySQL strings with no database connection

Dotan Cohen wrote:
>> If you look a little closer, you will see that I am not using addslashes().
>> Rather, I am using addcslashes(). This allows to specify the characters
>> that I want escaped, instead of the default assumed characters from
>> addslashes().
>>
>
> I do not know which characters to escape.
>

I have given you the link to the mysql_real_escape_string(). On that page, it
shows the characters that it escapes.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php