Hello,

The function mysql_real_escape_string() requires an open connection. The
MySQL manual says:

"This is needed because the escaping depends on the character set in use
by the server."

But a few lines further down, it says:

"Strictly speaking, MySQL requires only that backslash and the quote
character used to quote the string in the query be escaped."

Thus, only \ and ' or " would need to be prepended by a \. Isn't that
completely independent of the way these characters are encoded? So what
is connection needed for here?

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

On Fri, 04 Apr 2008 14:22:24 +0200, Thomas Mlynarczyk wrote:
> Hello,
>
> The function mysql_real_escape_string() requires an open connection. The
> MySQL manual says:
>
> "This is needed because the escaping depends on the character set in use
> by the server."
>
> But a few lines further down, it says:
>
> "Strictly speaking, MySQL requires only that backslash and the quote
> character used to quote the string in the query be escaped."
>
> Thus, only \ and ' or " would need to be prepended by a \. Isn't that
> completely independent of the way these characters are encoded? So what
> is connection needed for here?

There are complications, though, such as '' (to be interpreted as ')
doesn't need escaping. If fact, escaping it would be other than what is
probabaly really wanted. The C API knows all the magic rules, for the
version you're running, to do the right thing. I know I don't want to
rummage through piles of old code looking for by-hand escaping, nor
spend more time than I have to making changes before upgrading the
system.

--
10. I will not interrogate my enemies in the inner sanctum -- a small hotel
well outside my borders will work just as well.
--Peter Anspach's list of things to do as an Evil Overlord

Thomas Mlynarczyk wrote:
> Hello,
>
> The function mysql_real_escape_string() requires an open connection. The
> MySQL manual says:
>
> "This is needed because the escaping depends on the character set in use
> by the server."
>
> But a few lines further down, it says:
>
> "Strictly speaking, MySQL requires only that backslash and the quote
> character used to quote the string in the query be escaped."
>
> Thus, only \ and ' or " would need to be prepended by a \. Isn't that
> completely independent of the way these characters are encoded? So what
> is connection needed for here?
>
> Greetings,
> Thomas
>

No, it's not completely independent. For instance, if you're using a 16
bit charset, the combination may be something like x'205C'. In an 8 bit
charset this would be blank-backslash. But it could have an entirely
different meaning in a 16 bit charset.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle schrieb:

> No, it's not completely independent. For instance, if you're using a 16
> bit charset, the combination may be something like x'205C'. In an 8 bit
> charset this would be blank-backslash. But it could have an entirely
> different meaning in a 16 bit charset.

Okay, I understand that now. But wouldn't that also mean that I could do
the escaping myself in my PHP application? After all, PHP should know
how its own strings are encoded. Thus, I would only have to prepend
every \ and ' with a backslash, wrap the result in single quotes and
insert it into my qery. Or am I still missing something here?

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

Peter H. Coffin schrieb:

> There are complications, though, such as '' (to be interpreted as ')
> doesn't need escaping. If fact, escaping it would be other than what is
> probabaly really wanted.

If I have a string and I know it's not escaped yet, then '' would really
mean two quotes, so I see no ambiguity here.

> The C API knows all the magic rules, for the
> version you're running, to do the right thing.

I did not doubt that. It was just not quite clear to me how the encoding
could affect the escaping.

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

Thomas Mlynarczyk wrote:
> Jerry Stuckle schrieb:
>
>> No, it's not completely independent. For instance, if you're using a
>> 16 bit charset, the combination may be something like x'205C'. In an
>> 8 bit charset this would be blank-backslash. But it could have an
>> entirely different meaning in a 16 bit charset.
>
> Okay, I understand that now. But wouldn't that also mean that I could do
> the escaping myself in my PHP application? After all, PHP should know
> how its own strings are encoded. Thus, I would only have to prepend
> every \ and ' with a backslash, wrap the result in single quotes and
> insert it into my qery. Or am I still missing something here?
>
> Greetings,
> Thomas
>

Sure, you *could*. But can you guarantee that it will be correct if,
for instance, the charset changes? Can you guarantee you will be right
under all conditions? Remember - the problem it could cause wouldn't be
in PHP - it would be in MySQL.

What's your problem with using mysql_real_escape_string(), anyway?
That's exactly what it's there for. And you don't need to use it unless
you're dealing with the database anyway - in which case you have a
connection.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle schrieb:

[Escaping strings for MySQL in PHP, without mysql_real_escape_string()]
> Sure, you *could*. But can you guarantee that it will be correct if,
> for instance, the charset changes? Can you guarantee you will be right
> under all conditions?

The only problem I could imagine is that a future version of MySQL might
require more/other characters to be escaped or introduce another escape
character. But then, they might introduce another quote character and
then my PHP script would have to know about that, since
mysql_real_escape_string() doesn't wrap the string in quotes. But yes, I
admit that it is safer and cleaner to use that function.

> What's your problem with using mysql_real_escape_string(), anyway?

None. I was just wondering about the necessity for an existing connection.

> That's exactly what it's there for. And you don't need to use it unless
> you're dealing with the database anyway - in which case you have a
> connection.

Not necessarily. Example:

<?php
$sCity = mysql_real_escape_string( $_POST['city'] );
$sQuery = "SELECT * FROM table WHERE place = '$sCity'";
foreach ( new Query( $sQuery ) as $aRow )
{
// do something with each row
}
?>

The Query object connects to the DB and issues the query. But first the
$sQuery must be put together, and therefore all inserted data must be
properly escaped. In this case, the escape function does not have access
to any connection, because there isn't any yet.

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

Thomas Mlynarczyk wrote:
> Jerry Stuckle schrieb:
>
> [Escaping strings for MySQL in PHP, without mysql_real_escape_string()]
>> Sure, you *could*. But can you guarantee that it will be correct if,
>> for instance, the charset changes? Can you guarantee you will be
>> right under all conditions?
>
> The only problem I could imagine is that a future version of MySQL might
> require more/other characters to be escaped or introduce another escape
> character. But then, they might introduce another quote character and
> then my PHP script would have to know about that, since
> mysql_real_escape_string() doesn't wrap the string in quotes. But yes, I
> admit that it is safer and cleaner to use that function.
>

Which is *exactly* why you should be using mysql_real_escape_string.
But PHP doesn't necessarily need to know about it. Just use the
standard quote characters.

>> What's your problem with using mysql_real_escape_string(), anyway?
>
> None. I was just wondering about the necessity for an existing connection.
>

Now you know why.

>> That's exactly what it's there for. And you don't need to use it
>> unless you're dealing with the database anyway - in which case you
>> have a connection.
>
> Not necessarily. Example:
>
> <?php
> $sCity = mysql_real_escape_string( $_POST['city'] );
> $sQuery = "SELECT * FROM table WHERE place = '$sCity'";
> foreach ( new Query( $sQuery ) as $aRow )
> {
> // do something with each row
> }
> ?>
>
> The Query object connects to the DB and issues the query. But first the
> $sQuery must be put together, and therefore all inserted data must be
> properly escaped. In this case, the escape function does not have access
> to any connection, because there isn't any yet.
>
> Greetings,
> Thomas
>

If you want to do a proper OO architecture, you shouldn't have a SELECT
statement in your code. Rather, have an object which represents the
data in *some* external source - it may be a SQL database, flat file, or
something else. But the way you're doing it ties you to a SQL database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thomas Mlynarczyk wrote:
> Jerry Stuckle schrieb:
>
> [Escaping strings for MySQL in PHP, without mysql_real_escape_string()]
>> Sure, you *could*. But can you guarantee that it will be correct if,
>> for instance, the charset changes? Can you guarantee you will be
>> right under all conditions?
>
> The only problem I could imagine is that a future version of MySQL might
> require more/other characters to be escaped or introduce another escape
> character. But then, they might introduce another quote character and
> then my PHP script would have to know about that, since
> mysql_real_escape_string() doesn't wrap the string in quotes. But yes, I
> admit that it is safer and cleaner to use that function.
>
>> What's your problem with using mysql_real_escape_string(), anyway?
>
> None. I was just wondering about the necessity for an existing connection.
>
>> That's exactly what it's there for. And you don't need to use it
>> unless you're dealing with the database anyway - in which case you
>> have a connection.
>
> Not necessarily. Example:
>
> <?php
> $sCity = mysql_real_escape_string( $_POST['city'] );
> $sQuery = "SELECT * FROM table WHERE place = '$sCity'";
> foreach ( new Query( $sQuery ) as $aRow )
> {
> // do something with each row
> }
> ?>
>
> The Query object connects to the DB and issues the query. But first the
> $sQuery must be put together, and therefore all inserted data must be
> properly escaped. In this case, the escape function does not have access
> to any connection, because there isn't any yet.
>

You could use regex to clean your POST variable.

$sCity = (isset($_POST['city']) && eregi('[:alpha:]',$_POST['city'])) ?
$_POST['city'] : 'no_city_selected';

....should do nicely. Of course you need to handle errors accordingly.
Change the regex to your needs.

--
Norman
Registered Linux user #461062

Jerry Stuckle schrieb:

>> <?php
>> $sCity = mysql_real_escape_string( $_POST['city'] );
>> $sQuery = "SELECT * FROM table WHERE place = '$sCity'";
>> foreach ( new Query( $sQuery ) as $aRow )
>> {
>> // do something with each row
>> }
>> ?>

> If you want to do a proper OO architecture, you shouldn't have a SELECT
> statement in your code. Rather, have an object which represents the
> data in *some* external source - it may be a SQL database, flat file, or
> something else. But the way you're doing it ties you to a SQL database.

Well, I *must* have a SELECT statement *somewhere* in my code... But I
know what you mean:

<?php
$oBook = new Book();
$oBook->setTitle( 'Whatever' );
//...
$oBook->save();
?>

And thus I shouldn't need to worry how my book is stored in a database
or anywhere. But my Query object above is not supposed to be the "top
level" of the object hierarchy. Book would either inherit from Query or
I could use a Factory pattern or something like this. Query is just
meant to represent a query (or database or database server - I haven't
quite made up my mind yet).

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

Thomas Mlynarczyk wrote:
> Jerry Stuckle schrieb:
>
>>> <?php
>>> $sCity = mysql_real_escape_string( $_POST['city'] );
>>> $sQuery = "SELECT * FROM table WHERE place = '$sCity'";
>>> foreach ( new Query( $sQuery ) as $aRow )
>>> {
>>> // do something with each row
>>> }
>>> ?>
>
>> If you want to do a proper OO architecture, you shouldn't have a
>> SELECT statement in your code. Rather, have an object which
>> represents the data in *some* external source - it may be a SQL
>> database, flat file, or something else. But the way you're doing it
>> ties you to a SQL database.
>
> Well, I *must* have a SELECT statement *somewhere* in my code... But I
> know what you mean:
>
> <?php
> $oBook = new Book();
> $oBook->setTitle( 'Whatever' );
> //...
> $oBook->save();
> ?>
>
> And thus I shouldn't need to worry how my book is stored in a database
> or anywhere. But my Query object above is not supposed to be the "top
> level" of the object hierarchy. Book would either inherit from Query or
> I could use a Factory pattern or something like this. Query is just
> meant to represent a query (or database or database server - I haven't
> quite made up my mind yet).
>
> Greetings,
> Thomas
>

A Book is not a Query, so deriving Book from Query would be incorrect.

In fact, a Query is not an object - it is an action, so it would not be
a valid class candidate. Rather, it should be a method in a class.

Proper OO design and you don't have a problem.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle schrieb:

> A Book is not a Query, so deriving Book from Query would be incorrect.

Yes, of course, you are right.

> In fact, a Query is not an object - it is an action, so it would not be
> a valid class candidate.

But can't I think of a query as of an "abstract object" which I
instantiate with an SQL statement and retrieve a result? After all,
"query" is also a noun.

> Rather, it should be a method in a class.
> Proper OO design and you don't have a problem.

The problem is to get the OO design right. I think I'm familiar enough
with the theory, but when it comes to applying it to a real problem...

So, instead of "Query", I should have a DatabaseServer object which
receives SQL queries and returns data and status information. And from
that I derive a... Library object (or should that be a has-a relation?)
which has methods like storeNewBook(), retrieveBook() etc. and an
internal list of Book objects. - Is that the way to go about it?

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

Thomas Mlynarczyk wrote:
> Jerry Stuckle schrieb:
>
>> A Book is not a Query, so deriving Book from Query would be incorrect.
>
> Yes, of course, you are right.
>
>> In fact, a Query is not an object - it is an action, so it would not
>> be a valid class candidate.
>
> But can't I think of a query as of an "abstract object" which I
> instantiate with an SQL statement and retrieve a result? After all,
> "query" is also a noun.
>

What are the attributes of a query - besides the statement itself? This
is a common confusion factor between things which can be a noun or a
verb. "Display" is another common point of confusion.

But typically, if it *can* be a verb, it often *should* be a verb.

>> Rather, it should be a method in a class.
>> Proper OO design and you don't have a problem.
>
> The problem is to get the OO design right. I think I'm familiar enough
> with the theory, but when it comes to applying it to a real problem...
>

OO requires a completely different way of thinking. It's not easy for
experienced programmers to change to thinking in OO concepts. In fact,
I've found the longer someone has been programming, the longer it takes
to convert.

But even with relatively new programmers, it can take at least 6-12
months to really get into OO design.

> So, instead of "Query", I should have a DatabaseServer object which
> receives SQL queries and returns data and status information. And from
> that I derive a... Library object (or should that be a has-a relation?)
> which has methods like storeNewBook(), retrieveBook() etc. and an
> internal list of Book objects. - Is that the way to go about it?
>

Nope. What is your object? Your object is not necessarily a
DatabaseServer.

What you're interested in is the data in the database - in this case a
BOOK. That is a real object, and corresponds to anything from part of a
table to multiple tables in the database, depending on your implementation.

DatabaseServer *could* be an object - but it would only handle the
connection to the database server itself.

But all of this is off-topic for this newsgroup. You need to get into
one of the OO newsgroups to get a proper design generated. From that
you can implement your code.


> Greetings,
> Thomas
>


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle schrieb:

> But all of this is off-topic for this newsgroup. You need to get into
> one of the OO newsgroups to get a proper design generated. From that
> you can implement your code.

Yes, I will do that. Thanks for your suggestions.

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

> Okay, I understand that now. But wouldn't that also mean that I could do
> the escaping myself in my PHP application? After all, PHP should know
> how its own strings are encoded. Thus, I would only have to prepend
> every \ and ' with a backslash, wrap the result in single quotes and
> insert it into my qery. Or am I still missing something here?

Yes, you could. I did this for queue-based queries where there simply IS
no connection when you build the query. I whitelisted the normal SQL
characters and escaped every other character (and the slashes and
quotes). This means that string that cannot be escaped "normally" (by
adding a slash, for instance) are send as hexadecimal string.

BEWARE! hexadecimal strings are subject to encoding settings as well. I
had the greatest trouble that hexadecimal strings got re-encoded. The
way to overcome this is to explicitely state the encoding:

INSERT INTO sometable(sometext) VALUES(_utf8 0x.....);

But in normal circumstances you would use the mysql_real_escape_string()
function, because it does all this hard work for you.

>... because php knows how its strings are encoded....

No. MySQL should know how its strings are encoded AND knows. It
re-encodes strings that are in the "wrong" encoding (connection encoding
instead of table encoding and vise versa). And PHP does not always knows
the encoding. For normal posted forms, for instance, there is no browser
(tested with safari, firefox and IE7) that submits the encoding used.
Just test it. Post a form with some special characters. Change the
encoding in your browser and post again.

This may sound a bit nit-picking, but there are security issues possible
with encodings if you do not escape properly. So escape "to binary" and
do not take an encoding for granted on either side of the MySQL connection.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/