Re^2: Eval error on connectd DBI handle

Note that quoting or placeholder functions will not work for database identifiers like table names, and only for content. So code like:

my $query = $dbh->prepare('CREATE TABLE ?');
$query->execute('TABLE_NAME');
[download]

will fail because the database engine will render that as

CREATE TABLE 'TABLE_NAME'
[download]

which is nonsense syntax.

#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Comment on Re^2: Eval error on connectd DBI handle Select or Download Code

Replies are listed 'Best First'.
Re^3: Eval error on connectd DBI handle by afoken (Chancellor) on Feb 24, 2016 at 22:33 UTC
Note that quoting or placeholder functions will not work for database identifiers like table names DBI has a `quote_identifier()` method that handles all required quoting of database identifiers. Unfortunately, you can't use placeholders for identifiers, so you have to call `quote_identifier()` manually, like this: `my $cmd='create table '.$dbh->quote_identifier($tablename); $dbh->do($cmd);` [download] And by the way: There is also a quote() method in DBI. For all but some very exotic cases, just forget that it exists and use placeholders. I think quote() should `die` or at least `warn` when being called from non-DBI, non-DBD code. It is usually just wrong to use it instead of using placeholders. See Re: Counting rows Sqlite, Re^2: Massive Memory Leak, Re^3: Variable interpolation in a file to be read in, Re^5: Variable interpolation in a file to be read in for more details. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^3: Eval error on connectd DBI handle
by afoken (Chancellor) on Feb 24, 2016 at 22:33 UTC

Note that quoting or placeholder functions will not work for database identifiers like table names

DBI has a quote_identifier() method that handles all required quoting of database identifiers. Unfortunately, you can't use placeholders for identifiers, so you have to call quote_identifier() manually, like this:

my $cmd='create table '.$dbh->quote_identifier($tablename);
$dbh->do($cmd);
[download]

And by the way: There is also a quote() method in DBI. For all but some very exotic cases, just forget that it exists and use placeholders. I think quote() should die or at least warn when being called from non-DBI, non-DBD code. It is usually just wrong to use it instead of using placeholders. See Re: Counting rows Sqlite, Re^2: Massive Memory Leak, Re^3: Variable interpolation in a file to be read in, Re^5: Variable interpolation in a file to be read in for more details.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

[reply]
[d/l]
[select]