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

Hi, I'm creating a form that calls a perl script to add and modify information into a mysql table. Each input is defined into a variable, like:

$title = $in{'title'};

then this is printed to the mysql table when i call this script. However, whenever I add a quotation mark to the title (in the title field in the html form), say like Bro's Graduation, I get an error and it doesn't add to the sql table. may i ask for some suggestions on how to get around this problem? i've read some stuff by searching, but with no success. i clumsily created a subroutine for adding slashes (to escape the 's) for it but that did more harm than good.

please help! thank you!

Replies are listed 'Best First'.
Re: Escaping characters
by DamnDirtyApe (Curate) on Dec 01, 2002 at 21:14 UTC

    Your post doesn't make it clear exactly how you're accessing your MySQL database. The generally accepted way of doing this is with the DBD::mysql driver for the DBI module. If you use placeholders for your data, you don't need to worry about things like escaping quotes.


    _______________
    DamnDirtyApe 
    Those who know that they are profound strive for clarity. Those who
would like to seem profound to the crowd strive for obscurity.
            --Friedrich Nietzsche
[reply]
Re: Escaping characters
by graff (Chancellor) on Dec 01, 2002 at 21:26 UTC
    I gather that you are not using parameterized sql statements for doing the table updates, and you should be. You haven't provided enough code for anyone here to give a detailed reply about what needs to be done, but assuming that you are using the DBI module to perform the updates, then check out the part of the DBI man page that describes the functions "prepare", "bind_param" and "execute(@bind_values)".

    In a nutshell, you start with an update statement like 

    my $sth = $dbh->prepare("update my_table set title=? where key=?");

    [download]
    and then execute the statement with a list of scalar values like this: 
    $sth->execute( $in{title}, $in{key} );

    [download]
    No quotation marks are needed around the string values being handed to the database, and there is no need to escape things within the scalar values that happen to be quote characters.
[reply]
[d/l]
[select]
Re: Escaping characters
by adrianh (Chancellor) on Dec 01, 2002 at 21:27 UTC
    1. Show us the code
    2. Show us the error

    :-)

    My complete guess is that you're doing something like:

    $dbh->do("update ... title = '$title'");

    [download]

    In which case your extra apostrophie in the title is producing an illegal SQL statement.

    If this is what you're doing you want to look at 'Placeholders and Bind Values' in the DBI documentation for the right way to do it.

[reply]
[d/l]
Re: Escaping characters
by hawtin (Prior) on Dec 01, 2002 at 21:16 UTC

    Your problem probably is with the interpretation of the SQL command. If you are going to put this on to a web page you could try translating the quote character to '

[reply]
Re: Escaping characters
by drewboy (Sexton) on Dec 02, 2002 at 06:13 UTC
    Sorry... here are some codes, but i think i've already gotten my answer through your helpful posts (thanks!)... 
    sub exec_add_sql {
    
my $id= $in{'id'};
my $title = $in{'title'};
my $date = $in{'date'};
my $description = $in{'description'};
my $category = $in{'category'};

# connect to the database
my $dbh = DBI->connect("DBI:mysql:database:sql.server.com", user, pw);


#prepare the query
my $sth = $dbh->prepare("insert into postcards(id, title, date, descri
+ption, category)
values('$id', '$title', '$date', '$description', '$category')");

# execute the query
$sth->execute();

$sth->finish();    
$dbh->disconnect();

}

    [download]
    Do you think I should take out the 's from the values part? I will try it right now. Thanks for your help!
[reply]
[d/l]
      Do you think I should take out the 's from the values part? I will try it right now. Thanks for your help!
      Nope. Repace the variables that are being interpolated in your prepare statement with question marks (these are the "placeholders" mentioned previously), and add the variable names as parameters to the execute statement. There are plenty of examples in the Tutorials section and the DBI documentation, but in your case the code should be as follows: 
      my $sth = $dbh->prepare("insert into postcards(id, title, date, descri
+ption, category) values (?,?,?,?,?)");
$sth->execute($id, $title, $date, $description, $category);

      [download]

      rdfield

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom