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!)

In reply to Using SQL Placeholders correctly by perleager

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.