Author: spadkins
Date: Tue Nov 7 13:45:24 2006
New Revision: 8029
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/t/DBI-insert.t
p5ee/trunk/App-Repository/t/DBI-select.t
p5ee/trunk/App-Repository/t/DBI-update.t
Log:
use dbh->quote() to quote values instead of doing it half-way correctly
Modified: p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm Tue Nov 7 13:45:24 2006
@@ -629,6 +629,8 @@
my ($where, $column, $param, $value, $colnum, $repop, $sqlop, $column_def,
$quoted);
my ($tabledef, $tabcols, $alias, $dbexpr);
+ my $dbh = $self->{dbh};
+
$tabledef = $self->{table}{$table};
$alias = $tabledef->{alias};
$tabcols = $tabledef->{column};
@@ -754,15 +756,13 @@
$include_null = 0;
if ($repop eq "contains" || $repop eq "not_contains") {
- $value =~ s/'/\\'/g;
- $value = "'%$value%'";
+ $value = $dbh->quote("%" . $value . "%");
}
elsif ($repop eq "matches" || $repop eq "not_matches") {
+ $value = $dbh->quote($value);
$value =~ s/_/\\_/g;
- $value =~ s/'/\\'/g;
$value =~ s/\*/%/g;
$value =~ s/\?/_/g;
- $value = "'$value'";
}
elsif ($sqlop eq "in" || ($inferred_op && $sqlop eq "=")) {
if (! defined $value || $value eq "NULL") {
@@ -774,14 +774,13 @@
$include_null = 1;
}
if ($quoted) {
- $value =~ s/'/\\'/g;
+ $value = $dbh->quote($value);
if ($value =~ /,/ && !
$tabledef->{param}{$param}{no_auto_in_param}) {
$value =~ s/,/','/g;
- $value = "('$value')";
+ $value = "($value)";
$sqlop = "in";
}
else {
- $value = "'$value'";
$sqlop = "=";
}
}
@@ -806,14 +805,13 @@
$include_null = 1;
}
if ($quoted) {
- $value =~ s/'/\\'/g;
+ $value = $dbh->quote($value);
if ($value =~ /,/ && !
$tabledef->{param}{$param}{no_auto_in_param}) {
$value =~ s/,/','/g;
- $value = "('$value')";
+ $value = "($value)";
$sqlop = "not in";
}
else {
- $value = "'$value'";
$sqlop = "!=";
}
}
@@ -829,8 +827,7 @@
}
}
elsif ($quoted) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
$dbexpr = $column_def->{dbexpr};
if ($dbexpr && $dbexpr ne "$alias.$column") {
@@ -912,7 +909,9 @@
sub _mk_select_joined_sql {
&App::sub_entry if ($App::trace);
my ($self, $table, $params, $cols, $options) = @_;
+
$self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
+ my $dbh = $self->{dbh};
if (!defined $params || $params eq "") {
$params = {};
@@ -1363,15 +1362,13 @@
next if ($inferred_op && !$quoted && $value eq "");
if ($repop eq "contains" || $repop eq "not_contains") {
- $value =~ s/'/\\'/g;
- $value = "'%$value%'";
+ $value = $dbh->quote("%" . $value . "%");
}
elsif ($repop eq "matches" || $repop eq "not_matches") {
+ $value = $dbh->quote($value);
$value =~ s/_/\\_/g;
- $value =~ s/'/\\'/g;
$value =~ s/\*/%/g;
$value =~ s/\?/_/g;
- $value = "'$value'";
}
elsif ($sqlop eq "in" || ($inferred_op && $sqlop eq "=")) {
@@ -1384,14 +1381,13 @@
$include_null = 1;
}
if ($quoted) {
- $value =~ s/'/\\'/g;
+ $value = $dbh->quote($value);
if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
$value =~ s/,/','/g;
- $value = "('$value')";
+ $value = "($value)";
$sqlop = "in";
}
else {
- $value = "'$value'";
$sqlop = "=";
}
}
@@ -1417,14 +1413,13 @@
$include_null = 1;
}
if ($quoted) {
- $value =~ s/'/\\'/g;
+ $value = $dbh->quote($value);
if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
$value =~ s/,/','/g;
- $value = "('$value')";
+ $value = "($value)";
$sqlop = "not in";
}
else {
- $value = "'$value'";
$sqlop = "!=";
}
}
@@ -1440,8 +1435,7 @@
}
}
elsif ($quoted) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
}
@@ -1667,9 +1661,11 @@
sub _mk_insert_row_sql {
&App::sub_entry if ($App::trace);
my ($self, $table, $cols, $row) = @_;
+
$self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
- my ($sql, $values, $col, $value, $colnum, $quoted);
+ my $dbh = $self->{dbh};
+ my ($sql, $values, $col, $value, $colnum, $quoted);
#print "_mk_insert_row_sql($table,\n [",
# join(",",@$cols), "],\n [",
# join(",",@$row), "])\n";
@@ -1695,8 +1691,7 @@
else {
$quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
}
}
@@ -1717,7 +1712,9 @@
sub _mk_insert_sql {
&App::sub_entry if ($App::trace);
my ($self, $table, $cols, $row, $options) = @_;
+
$self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
+ my $dbh = $self->{dbh};
if (!ref($cols)) {
$cols = [ $cols ];
@@ -1748,8 +1745,7 @@
else {
$quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
if ($tabcols->{$col}{dbexpr_update}) {
$value = sprintf($tabcols->{$col}{dbexpr_update}, $value);
@@ -1774,6 +1770,7 @@
die "Database->_mk_update_sql(): no columns specified" if (!$cols ||
$#$cols == -1);
$self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
+ my $dbh = $self->{dbh};
my $tabcols = $self->{table}{$table}{column};
my $by_expression = $options->{by_expression};
@@ -1800,8 +1797,7 @@
if (!ref($col)) {
$quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted && !$by_expression) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
$where = "where $col = $value\n";
$noupdate{$col} = 1;
@@ -1815,8 +1811,7 @@
$value = $values[$colidx];
$quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted && !$by_expression) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
push(@where, "$col = $value");
$noupdate{$col} = 1;
@@ -1846,8 +1841,7 @@
else {
$quoted = (defined
$tabcols->{$col}{quoted})?($tabcols->{$col}{quoted}):($value !~ /^-?[0-9.]+$/);
if ($quoted) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
}
}
@@ -1886,8 +1880,7 @@
else {
$quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted && !$by_expression) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
if ($tabcols->{$col}{dbexpr_update}) {
$value = sprintf($tabcols->{$col}{dbexpr_update}, $value,
$value, $value, $value, $value);
@@ -1914,6 +1907,7 @@
my ($self, $table, $params, $cols, $row, $options) = @_;
$self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
+ my $dbh = $self->{dbh};
my $tabcols = $self->{table}{$table}{column};
my $by_expression = $options->{by_expression};
@@ -1941,8 +1935,7 @@
if (!ref($col)) {
$quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted && !$by_expression) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
$where = "where $col = $value\n";
}
@@ -1955,8 +1948,7 @@
$value = $values[$colidx];
$quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted && !$by_expression) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
push(@where, "$col = $value");
}
@@ -1985,8 +1977,7 @@
else {
$quoted = (defined
$tabcols->{$col}{quoted})?($tabcols->{$col}{quoted}):($value !~ /^-?[0-9.]+$/);
if ($quoted) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
}
}
@@ -2010,7 +2001,10 @@
sub _mk_delete_row_sql {
&App::sub_entry if ($App::trace);
my ($self, $table, $cols, $row, $keycolidx) = @_;
+
$self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
+ my $dbh = $self->{dbh};
+
my ($sql, $where, @colused, $col, $value, $colnum, $i, $nonkeycolnum,
$quoted);
if ($#$cols == -1) {
$self->{error} = "Database->_mk_delete_row_sql(): no columns
specified";
@@ -2037,8 +2031,7 @@
else {
$quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
+ $value = $dbh->quote($value);
}
}
}
@@ -2534,6 +2527,8 @@
# METADATA REPOSITORY METHODS (implements methods from App::Repository)
######################################################################
+# REMOVE ALL DEPENDENCE ON DBIx::Compat
+# (ok. I want to, but I'm not ready to rewrite ListFields.)
use DBIx::Compat;
sub _load_rep_metadata_from_source {
@@ -2556,13 +2551,14 @@
# get a list of the physical tables from the database
# in MySQL 4.0.13, the table names are surrounded by backticks (!?!)
# so for safe measure, get rid of all quotes
- @tables = grep(s/['"`]//g, $dbh->tables);
+ @tables = grep(s/['"`]//g, $dbh->tables(undef, undef, undef, "TABLE"));
+ # REMOVE ALL DEPENDENCE ON DBIx::Compat
# if the DBI method doesn't work, try the DBIx method...
- if ($#tables == -1) {
- $func = DBIx::Compat::GetItem($dbdriver, "ListTables");
- @tables = &{$func}($dbh);
- }
+ # if ($#tables == -1) {
+ # $func = DBIx::Compat::GetItem($dbdriver, "ListTables");
+ # @tables = &{$func}($dbh);
+ # }
# go through the list of native tables from the database
foreach $table (@tables) {
@@ -2718,13 +2714,14 @@
#########################################################
# DATABASE ATTRIBUTES
#########################################################
- $self->{native}{support_join} = DBIx::Compat::GetItem($dbdriver,
"SupportJoin");
- $self->{native}{inner_join_syntax} = DBIx::Compat::GetItem($dbdriver,
"SupportSQLJoin");
- $self->{native}{inner_join_only2tables} = DBIx::Compat::GetItem($dbdriver,
"SQLJoinOnly2Tabs");
- $self->{native}{have_types} = DBIx::Compat::GetItem($dbdriver,
"HaveTypes");
- $self->{native}{null_operator} = DBIx::Compat::GetItem($dbdriver,
"NullOperator");
- $self->{native}{need_null_in_create} = DBIx::Compat::GetItem($dbdriver,
"NeedNullInCreate");
- $self->{native}{empty_is_null} = DBIx::Compat::GetItem($dbdriver,
"EmptyIsNull");
+ # REMOVE ALL DEPENDENCE ON DBIx::Compat
+ # $self->{native}{support_join} =
DBIx::Compat::GetItem($dbdriver, "SupportJoin");
+ # $self->{native}{inner_join_syntax} =
DBIx::Compat::GetItem($dbdriver, "SupportSQLJoin");
+ # $self->{native}{inner_join_only2tables} =
DBIx::Compat::GetItem($dbdriver, "SQLJoinOnly2Tabs");
+ # $self->{native}{have_types} =
DBIx::Compat::GetItem($dbdriver, "HaveTypes");
+ # $self->{native}{null_operator} =
DBIx::Compat::GetItem($dbdriver, "NullOperator");
+ # $self->{native}{need_null_in_create} =
DBIx::Compat::GetItem($dbdriver, "NeedNullInCreate");
+ # $self->{native}{empty_is_null} =
DBIx::Compat::GetItem($dbdriver, "EmptyIsNull");
&App::sub_exit() if ($App::trace);
}
@@ -2769,6 +2766,8 @@
my ($colnum, $data_types, $columns, $column_def, $phys_columns);
my ($native_type_num, $native_type_def, $phys_table);
+ # REMOVE ALL DEPENDENCE ON DBIx::Compat
+ # (ok. I want to, but I'm not ready to rewrite ListFields.)
$func = DBIx::Compat::GetItem($dbdriver, "ListFields");
eval {
$sth = &{$func}($dbh, $table);
Modified: p5ee/trunk/App-Repository/t/DBI-insert.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-insert.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-insert.t Tue Nov 7 13:45:24 2006
@@ -146,6 +146,16 @@
});
};
ok($@, "insert dup hash in 2nd pos fails");
+
+ ok($db->insert("test_person", undef, {
+ person_id => 11,
+ age => 999,
+ first_name => '[EMAIL PROTECTED]'',
+ gender => "M",
+ state => "GA",
+ }),
+ "insert \\ and ' and \\' seems to work");
+ is($db->get("test_person",11,"first_name"),'[EMAIL PROTECTED]'', "yep.
first_name worked.");
}
exit 0;
Modified: p5ee/trunk/App-Repository/t/DBI-select.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select.t Tue Nov 7 13:45:24 2006
@@ -490,6 +490,15 @@
is($sql, $expect_sql, "_mk_select_sql(): param.matches (inferred)");
&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person",{
+ "_order" => [ "first_name", "age", "birth_dt", ],
+ "first_name" => "*s*e?",
+ "age" => "*3",
+ "birth_dt" => "1962_*",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.matches (inferred)");
+&check_select($sql,0);
+
$expect_sql = <<EOF;
select
first_name
@@ -622,6 +631,23 @@
$sql = $rep->_mk_select_sql("test_person", { "first_name.not_in" =>
"stephen,keith,NULL", }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is not null (by .not_in
'stephen,keith,NULL')");
&check_select($sql,0);
+
+$expect_sql = <<'EOF';
+select
+ first_name
+from test_person
+where first_name like '%\'%'
+ and birth_dt like '%\\\'_'
+EOF
+#print "[$expect_sql]\n";
+$sql = $rep->_mk_select_sql("test_person",{
+ "_order" => [ "first_name.contains", "birth_dt.matches", ],
+ "first_name.contains" => "'",
+ "birth_dt.matches" => "*\\'?",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.contains (proper quoting of '
and \\' required)");
+&check_select($sql,0);
+
exit(0);
$expect_sql = <<EOF;
Modified: p5ee/trunk/App-Repository/t/DBI-update.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-update.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-update.t Tue Nov 7 13:45:24 2006
@@ -138,6 +138,15 @@
is($sql, $expect_sql, "_mk_update_sql(): 2 cols, by key, row is a hashref");
&check_exec($sql,1);
+$expect_sql = <<'EOF';
+update test_person set
+ first_name = '[EMAIL PROTECTED]''
+where person_id = 4
+EOF
+$sql = $rep->_mk_update_sql("test_person",4,["first_name"],['[EMAIL
PROTECTED]'']);
+is($sql, $expect_sql, "_mk_update_sql(): proper quoting for \\'");
+&check_exec($sql,1);
+
# This doesn't work yet
#$expect_sql = <<EOF;
#update test_person set
