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

[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 :)