Re: Quote String

On Fri, Jul 06, 2001 at 03:02:21AM +0200, Tomas V.V.Cox wrote: > Paul DuBois wrote: > > > > At 8:02 PM +0200 7/3/01, Tomas V.V.Cox wrote: > > >Paul DuBois wrote: > > >> > > >> At 11:19 AM +0200 7/3/01, Tomas V.V.Cox wrote: > > >> >Sorry for not to follow the thread but I recently have suscribed to the > > >> >list. Please test this patch to the pear/DB/mysql.php, so if works I'll > > >> >commit the change to the quote string behavoir. The same for the sybase > > >> >extension. > > >> > > > >> >For the question about null values I don't see any way for inserting > > >> >them, but the needed change in executeEmulateQuery() for supporting it > > >> >is trivial. What do you think to add the unquoted NULL string when the > > >> >null (constant) value is given? > > >> > > >> Personally, I think that's the corrrect behavior. But on the other hand, > > >> it wouldn't be so difficult to make quoteString() do that to, such that: > > >> > > >> quoteString("a") => "'a'" > > >> quoteString (null) => "NULL" > > >> > > > > > >Yeah, great idea, but it will require a little more work. Hope to have > > >some time in the next days to start with it, if noone have anything more > > >to say. > > > > I guess the question isn't as simple as I thought. Initially, I > > thought this might do it: > > > > // {{{ quoteString() > > > > function quoteString($string) > > { > > return ($string == null ? "NULL" : mysql_escape_string($string)) ; > > } > > The correct sintaxis should be: > > return ($string === null) ? "NULL" : mysql_escape_string($string); I don't see any semantic difference. They both return the same result. > > However, the problem is that quoteString() doesn't add quotes > > around non-NULL values. So it doesn't really act like DBI's quote() > > at all. > > Why not? mysql_escape_string() problem? It's not a problem of implementation, really, just a difference of intent. mysql_escape_string() is intended to make the string safe for inserting between quotes in a query. The result doesn't include the surrounding quotes. In DBI, the result from quote() does include the surrounding quotes (except that if you pass it undef, the result is the string NULL without quotes). > > > Having quoteString() act like I initially suggested would > > involve a change to PEAR that's probably too incompatible with existing > > behavior, so I guess it shouldn't try to handle NULL at all. (All other > > quoteString() versions would have to change, and executeEmulateQuery() > > in common.php would need to change the way it uses quoteString().) > > I think that have this would be very nice. It would give us safe queries > and save the developers to type a lot of extra code. Change execEmu or > extensions is not too much work. Also the compat won't change with that. Okay. Then my view would be to have quoteString() return a string NULL without quotes if passed a null argument, and to return a properly escaped string *with* surrounding quotes otherwise. Then the caller wouldn't have to be checking for the special case of null, and could uniformly insert the result from quoteString() into a query string without regard to the form of its argument. > > Just for curiosity I take a look at the DBI quote system. It's quite > impresive (at least the postgres one). They sure have implemented quote > in all the backends, so just cut & paste into Pear (love open source :). > For the curious I've pasted at the end the DBI Postgres quote. > > Tomas V.V.Cox > > > DBI Pg.pm > ------------------------------ > sub quote { > my ($dbh, $str, $data_type) = @_; > > return "NULL" unless defined $str; > > unless ($data_type) { > $str =~ s/'/''/g; # ISO SQL2 > # In addition to the DBI method it doubles also the > # backslash, because PostgreSQL treats a backslash as an > # escape character. > $str =~ s/\\/\\\\/g; > return "'$str'"; > } > > # Optimise for standard numerics which need no quotes > return $str if $data_type == DBI::SQL_INTEGER > || $data_type == DBI::SQL_SMALLINT > || $data_type == DBI::SQL_DECIMAL > || $data_type == DBI::SQL_FLOAT > || $data_type == DBI::SQL_REAL > || $data_type == DBI::SQL_DOUBLE > || $data_type == DBI::SQL_NUMERIC; > my $ti = $dbh->type_info($data_type); > # XXX needs checking > my $lp = $ti ? $ti->{LITERAL_PREFIX} || "" : "'"; > my $ls = $ti ? $ti->{LITERAL_SUFFIX} || "" : "'"; > # XXX don't know what the standard says about escaping > # in the 'general case' (where $lp != "'"). > # So we just do this and hope: > $str =~ s/$lp/$lp$lp/g > if $lp && $lp eq $ls && ($lp eq "'" || $lp eq > '"'); > # also, escape the backslashes, always > $str =~ s/\\/\\\\/g; > # if the type is SQL_BINARY, escape the non-printable chars > if ($data_type == DBI::SQL_BINARY || > $data_type == DBI::SQL_VARBINARY || > $data_type == DBI::SQL_LONGVARBINARY) { > $str=join("", map { > isprint($_)?$_:'\\'.sprintf("%03o",ord($_)) } > split //, $str); > } > return "$lp$str$ls"; > } > > -- > PEAR Development Mailing List (http://pear.php.net/) > To unsubscribe, e-mail: pear-dev-unsubscribe@lists.php.net > For additional commands, e-mail: pear-dev-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net