Using SQL Placeholders correctly

perleager has asked for the wisdom of the Perl Monks concerning the following question:

Hey all,

I just read some interesting things about placeholders on this site.

I wouldn't say im a good good programmer, but a programmer that is continuing to learn and learn. Hopefully two or three more years of experience will stop me from coming to this board and asking so many questions!

I recently posted a message on how I couldn't get my insert code working with a BLOB column type. I'm making a note program that sends memos to each users, and memos could be way longer then 255 characters so the varchar type wouldn't do.

Now onto placeholders...I got several replys saying I wasn't using placeholders and that my insert code shouldn't work because it had no placeholders.
Original code: (Someone replied to me saying I'm using one placeholder and that this code could not have possibly worked for me whether I had the column type as varchar or blob)

$time = time;
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($tim
+e);
$mon++;
$year += 1900;

$memo = "Original Message\n------------\n$INPUT{'memo'}";
$memo_final = $memo . "\n\nCreated by: $username\nDate Created: $year-
+$mon-$mday\n\n";

use DBI;


     my $dbh_m = DBI->connect('DBI:mysql:MEMO') or print "Couldn't con
+nect to database: " .         
        DBI->errstr;

 $sql_m = "
  INSERT 
  INTO memo
  VALUES
  (
'?',
'$year-$mon-$mday',
'$INPUT{'user'}',
'$INPUT{'ref'}',
'$INPUT{'username'}',
'notread',
'$memo_final'
  )
";

 $sth_m = $dbh_m->prepare($sql_m) or print "preparing: ",$dbh_m->errst
+r;
 $sth_m->execute or print "executing: ", $dbh_m->errstr;
 $sth_m->finish;
$dbh_m->disconnect; 

&header;
print <<EOF;
<B>Memo to $INPUT{'user'} successfully added.</b>
<br>
EOF
&footer;
[download]

Now, I got TOTALLY confused when I read this message and acutally got a rep of -2 when I replied questioning about it.
Now I read more about placeholders on this site and now I know what he meant by saying you are using one placeholder. The ? for the id field is for the auto_increment. Is that not how you specify the value for a auto_increment field?
What made me even more confused is that this code did insert fine without any sql errors, etc with the column type being varchar.
When I decided to alter the table to make the memo_text field a BLOB instead of varchar, then I started to recieve errors on the sql syntax.

Back to the topic placeholders,
I realized the significance on placeholders so I decided to change my coding to use them! :) If I remember correctly, placeholders will solve any escape character issues such as using '" inside the typed memo from the user.

$time = time;
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($tim
+e);
$mon++;
$year += 1900;

my $memo = "Original Message\n------------\n$INPUT{'memo'}";
my $memo_final = "$memo" . "\n\nCreated by: $username\nDate Created: $
+year-$mon-$mday\n\n";

use DBI;

     my $dbh_m = DBI->connect('DBI:mysql:MEMO') or print "Couldn't con
+nect to database: " .         
        DBI->errstr;
DBI->trace(2);

my $sth_m = $dbh_m->prepare(<<SQL);
INSERT INTO memo (id, date, user, ref, creator, status, memo_text) val
+ues (?, ?, ?, ?, ?, ?, ?)
SQL

$sth_m->execute('?', '$year-$mon-$mday', '$INPUT{'user'}', 'INPUT{'ref
+'}', '$INPUT{'username'}', 'notread', '$memo_final');   #line 148
$sth_m->finish;
$dbh_m->disconnect; 
DBI->trace(0);

&header;
print <<EOF;
<B>Memo to $INPUT{'user'} successfully added.</b>
<br>
EOF
&footer;
[download]

Now this code doesn't work and I'm not sure why. Am I using placeholders correctly?

Returns a error saying "Bad name after user' at C:\wwwroot\memo.pl line 148."

Any help will be appreciated :)
Thank you,
Anthony

update (broquaint): title change (was Placeholders!)

Comment on Using SQL Placeholders correctly Select or Download Code

Replies are listed 'Best First'.
Re: Using SQL Placeholders correctly by grinder (Bishop) on Dec 21, 2003 at 22:21 UTC
The ? for the id field is for the auto_increment. Is that not how you specify the value for a auto_increment field? For an auto_increment, or serial, field, you don't have to do anything at all. Just do the following: `my $sth_m = $dbh_m->prepare(<<SQL) or die $dbh_m->errstr; INSERT INTO memo (date, user, ref, creator, status, memo_text) values (?, ?, ?, ?, ?, ?) SQL $sth_m->execute("$year-$mon-$mday", $INPUT{user}, INPUT{ref}, $INPUT{username}, 'notread', $memo_final) or die $sth_m->errstr;` [download] That is, don't do anything about specifying or filling in the id field. The database will fill it in for you.	[reply] [d/l]
Re: Using SQL Placeholders correctly by jZed (Prior) on Dec 21, 2003 at 21:47 UTC
*Placeholders are instead* of quoteing. Change: $sth_m->execute('?', '$year-$mon-$mday', '$INPUT{'user'}'... to $sth_m->execute(?,"$year-$mon-$mday", $INPUT{'user'}... And if $INPUT{'user'} is directly from a CGI form or outside input, be sure to untaint it before using it in a SQL statement. See perlsec on taining. and what is $year-$mon-$day, a concatentation of variables into a string? If so, it should be "$year-mon-dy" in the exectute() - those are double perl quotes to interpolote the variables, not single SQL quote to delimite a vlaue. Updated** removed the ? from the execute since grinder is right that is the autoincrement which is done automatically. Added double quotes aroung $year-month-day to make it match what I said below it about needing the double quotes to interpolate the variable ... thanks bart for pointing out my sloppiness.	[reply]
Re: Using SQL Placeholders correctly by liz (Monsignor) on Dec 21, 2003 at 21:39 UTC
$sth_m->execute('?', '$year-$mon-$mday', '$INPUT{'user'}', 'INPUT{'ref'}', '$INPUT{'username'}', 'notread', '$memo_final') You will want to use double quotes around anything that contains variables, and lose any quotes around the simple scalar values, you don't need any quotes there! `$sth_m->execute('?', "$year-$mon-$mday", $INPUT{'user'}, $INPUT{'ref'} +, $INPUT{'username'}, 'notread', $memo_final);` [download] Liz	[reply] [d/l]
Re: Re: Using SQL Placeholders correctly by pg (Canon) on Dec 21, 2003 at 23:29 UTC
What does that first '?' mark mean? It makes no sense unless you are setting the value of id to '?'. ? as placeholder is only meaningful when you prepare the statement, not at the time you execute it. I think you missed the true meaning of placeholder. For auto-increment field, you don't set value for it, otherwise what does that'auto-' mean? just take that field out from the insert statement, and database will fill in the right value for you.	[reply]
Placeholder confusion .. and other items by talexb (Chancellor) on Dec 22, 2003 at 04:28 UTC
You raise a number of issues in your post, so I'll try to reply to a few of them here. A BLOB column type is for binary code -- I think BLOB stands for Binary Large OBject. To store a big chunk of text (like the MEMO field you mentioned), just use a text field. It's pretty roomy. Now, I got TOTALLY confused when I read this message and acutally got a rep of -2 when I replied questioning about it. You are probably being penalized by some monks by posting a chunk of code without showing that you've exhausted all of your available resources. Am I using placeholders correctly? Check the documentation for DBI .. placeholders are explained well there. Here's a tip: when you get stuck in the future, try the simplest possible example, and get that to fail. Post that simple example and ask for an explanation .. assuming (of course) that you've already combed the available man pages and did a bit of scratching through the database here on Perl Monks. You will get negative votes if you post without having done any research on your questions. It's important to learn how to learn. We'll help out when the path gets hard to find, but placeholders are not some arcane aspect of database programming, they're actually just good, basic, common sense. Good luck! --t. alex Life is short: get busy!	[reply]
Re: Using SQL Placeholders correctly by cLive ;-) (Prior) on Dec 22, 2003 at 03:40 UTC
...BLOB column type. I'm making a note program that sends memos to each users, and memos could be way longer then 255 characters so the varchar type wouldn't do... Err, this could just be me, but is there any reason you're not using the TEXT field type if it's just a memo? Similar to BLOB, but sorts are case insensitive and it's intuitive that the field contains text See here. .02 cLive ;-)	[reply]
Re: Using SQL Placeholders correctly by Anonymous Monk on Dec 22, 2003 at 07:34 UTC
Now, I got TOTALLY confused when I read this message and acutally got a rep of -2 when I replied questioning about it. Hi. I downvoted you just for saying that. Don't be a big XP baby. Good luck.	[reply]