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

I'm running into a problem with uploading a <TEXTAREA> field from a web form into a TEXT column type in Mysql. Here's an example...
my $c = CGI->new();
my $node_id = $c->Vars()->{nodeid}
my $text = $c->Vars()->{textdata};
my $sth = $dbh->prepare("INSERT INTO t2d SET textfield=? WHERE node_id
+=?");
$sth->execute($text,$sth);

[download]
This generates a syntax error every time. I'm guessing that quotes, newlines, etc. in the <TEXTAREA> are causing the problem, but I've scoured all the docs, super search data and categorized questions and can't find the answer.
Is there a simple routine I'm missing to "safe" the text before trying to insert it into the database? Am I missing something else?

Thanks in advance.
cluka
JA(beginning)PH

Replies are listed 'Best First'.
Re: On Handling Text (Blob) Column Types in MySQL/CGI
by dws (Chancellor) on Apr 03, 2003 at 22:56 UTC
    That INSERT query looks wacky. Try 
      my $sth = $dbh->prepare("INSERT INTO t2d (textfield, node_id) VALUES
+(?, ?)");

    [download]
    or, if you really are updating an existing record, try 
      my $sth = $dbh->prepare("UPDATE t2d SET textfield=? WHERE node_id=?"
+);

    [download]
[reply]
[d/l]
[select]
Re: On Handling Text (Blob) Column Types in MySQL/CGI
by jasonk (Parson) on Apr 03, 2003 at 22:28 UTC

    You're missing that $sth doesn't contain a number:

    $sth->execute($text,$node_id);

    [download]

    Also, there are some situations (such as fields with multiple values) where Vars() will cause you problems. The way you are using it, you are better off writing those two lines as:

    my $node_id = $c->param('nodeid');
my $text = $c->param('textdata');

    [download]
    We're not surrounded, we're in a target-rich environment!
[reply]
[d/l]
[select]
      Sorry about that - my typo. I meant to say
      ...
$sth->execute($text,$node_id);
...

      [download]
      cluka
[reply]
[d/l]

        The other problem (which I seem to have missed the first time around) is that you are mixing query types, is it an INSERT, or is it an UPDATE? You can't have a WHERE clause for an INSERT (although mysql has a REPLACE INTO syntax which does what you may be trying to do here).

        We're not surrounded, we're in a target-rich environment!
[reply]
Re: On Handling Text (Blob) Column Types in MySQL/CGI
by benn (Vicar) on Apr 04, 2003 at 09:22 UTC
    The simple routine to 'safe' the text wrt quotes etc. is $dbh->quote($text)

    Cheers,
    Ben.
    update doh doh doh dohhhh....I knew that...that'll teach me to not skip the code and just read the description<g> - thanks hb
[reply]
[d/l]

      Using placeholders does that for you automagically.

      ----
      I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
      -- Schemer

      Note: All code is untested, unless otherwise stated

[reply]

Back to Seekers of Perl Wisdom