Writing to the database through a Perl script in Dreamwidth, placeholders

[anarres]

In Dreamwidth you can interact with the database using Perl's DBI module, but with a few Dreamwidth-specific methods layered on top. I've been trying to figure out how to write new rows to a database table. After looking at some other Dreamwidth scripts to see how it's done, I wrote the following script (which doesn't work):

# -------------------- ~/dw/bin/dev/test.pl ----------------------------
#!/usr/bin/perl

use lib "$ENV{LJHOME}/cgi-bin";
require 'ljlib.pl';
use strict;
use warnings;

my $cart = {
authcode => 'blahblahblahblah',
userid => 12,
cartid => 33,
};

my $dbh = LJ::get_db_writer() or return undef;

$dbh->do(
q{INSERT INTO payments (userid, cartid) VALUES ( ?, ?)},
$cart->{userid}, $cart->{cartid}
);

die "Database error: " . $dbh->errstr . "\n" if $dbh->err;
# ---------------------------------------------------------------------

The question marks are SQL placeholders, which prevent SQL injection attacks.

When I ran this I got the error: 'DBI::db=HASH(0x426eff0)->do(...): attribute parameter '12' is not a hash ref at test.pl line 22.'

It seemed to be complaining that $cart->{userid} is not a hash ref. I had no idea why it would want a hash ref there, but I bemusedly decided to give it what it wanted, and replaced

$cart->{userid}, $cart->{cartid}

with:

$cart, $cart

Running the script again gave an SQL error instead of a Perl error: 'Database error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2'.

So it seems like giving it a hash ref is the right thing to do, but I'm giving it the wrong kind of hash ref. I'm completely confused: what's the right syntax for inserting a row into the database?

Comments

[aphenine]

Check the examples again. When calling DBI::do() I remember seeing the following call order used in LJ code:

("INSERT INTO something (id1, id2) VALUES ( ?, ?)", null, parameter 1, parameter 2)

I don't know what the null does, but it's there in lots of LJ code before the parameters are listed and I'm sure it's what's causing your problem.

[anarres]

Ah, thanks, that did the trick.

For the record, I replaced the line

$cart->{userid}, $cart->{cartid}

with:

undef, $cart->{userid}, $cart->{cartid}

to get it working.

[aphenine]

*sigh* I keep forgetting Perl doesn't have null but undef. Maybe one day I'll remember? No? Didn't think so...

Glad it helped :)

[mark]

In case you care, the second parameter (the undef you were missing) is basically an "options" parameter. You can tell DBI to do some interesting things with that parameter by passing in a hashref with some options.

We rarely if ever user it, but it's required...

[aphenine]

Thank you. I was going to look it up one day :)