$dbh->quote(..., SQL_INTEGER)

[powerman]

[hv]

1. is this a bug in $dbh->quote()

This cannot be answered without referring to the documentation. There it says:

As a special case, the standard numeric types are optimized to return $value without calling type_info.

The answer is therefore: no, this is expected documented behaviour, not a bug. You could however argue that it is a wart - having gone to the trouble of providing a quoting mechanism, it seems a bit unhelpful to exempt these types as a special case.

2. is there exists more correct way to insert numbers into SQL

All I can suggest is to create your own quoting method. Something like:

  sub quote_number {
    my($dbh, $value) = @_;
    if (defined $value) {
      # force to number
      return $value + 0;
    } else {
      return $dbh->quote($value);
    }
  }

Note that as written, that will give a warning (assuming they're turned on) on anything that doesn't look enough like a number; you may prefer to treat that more severely (die rather than warn) or less severely (don't warn at all); similarly the above code will permit decimals and negative numbers: if you want to be more restrictive than that you'll need to add some code to test either mathematically or with regexps, eg:

  sub quote_integer {
    my($dbh, $value) = @_;
    if (defined $value) {
      # pattern approach
      die "Not an integer" unless $value =~ /^[-+]?\d+\z/;
      # maths approach
      die "Not an integer" unless $value == int($value);
      return $value + 0;
    } else {
      return $dbh->quote($value);
    }
  }

Hugo

[powerman]

Thanks for your answer!

What do you think about this idea: redefine $dbh->quote() by this logic:
1. check perl internals (SV) to find is my param a number or string
2. if it's a number - return something like yours quote_integer(), else return original $dbh->quote()
This way we will be able to control $dbh->quote() bevaviour this way:

$dbh->do("INSERT ... SELECT ... WHERE a=? LIMIT ?", undef,
    $a, 0+$limit);

[hv]

Checking the internals of an SV is almost always the wrong thing to do.

If you're happy to pass 0+$limit without checking for problems, you might as well interpolate that straight into the SQL rather than missing with quote().

Hugo

[ikegami]

$dbh->quote() only takes one argument:
$quoted_string = $dbh->quote($string);

Validating against \d+ seems perfectly reasonable to me, since I can't think of any way to do it using DBI. (Placeholders don't work in LIMIT, IIRC, but you could try that.)

Update: oops, reading further down the docs, quote does allow two arguements. However, quote() quotes (makes safe), it does not validate (makes valid).

[tadamec]

When dealing with parameter passing in DBI, it's often best to use '?' style replacements in the DBI engine. Most (*grumble*FreeTDS*grumble*) of the DBD drivers support this.

So, something like this is what I normally do:

my @values = ( 'SomeValue', 5 );
my $dbh= DBI->connect( $DSN, $user, $pass );
my $SQL = "SELECT * FROM mytable WHERE field1=? LIMIT ?";
my $sth = $dbh->prepare($SQL);
$sth->execute( @values );

The 'execute" method of a statement handle expects a standard list, so this is acceptable as well:

$sth->execute( 'SomeValue', 42 );

The nice thing about using the bind parameters is that DBI will automagically quote your values for you. You are responsible for data validation, as is true with most applications. Your solution of using a regular expression to perform data validation is how I usually do it.

[iburrell]

LIMIT clauses can be different with placeholders. The number is sometimes considered not a normal data value. Some databases, like MS-SQL, don't support placeholders in the LIMIT clause.

With PostgreSQL and DBD::Pg, the placeholders are substituted on the client. Without bind_param types, they use strings which are converted for all normal types. This doesn't work for LIMIT clause which must be an unquoted number. Supposedly, setting the type with bind_param will work:

my $sth = $dbh->prepare("SELECT * FROM big_table LIMIT ?");
$sth->bind_param(1, $value, SQL_INTEGER);
$sth->execute(10);

I don't know what will happen when the server-side parameters are implemented.