Re: Quote String

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); > 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? > 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. 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"; }