FAQ
Damn, that's a mouthful. Hopefully someone understands it ;) I'm writing a script to take
an existing data source (tab delimeted text file exported from Word doc w/ table) and
insert the data into a MySQL DB using DBI. My problem is that some of the fields are
either quoted ("") or have apostrophes ('), so I can't quote the data in the query without
something fouling up (MySQL chokes on the query because of the weird quoting). Can someone
help?

$dbh->do("INSERT INTO people VALUES(NULL, '$parts[1]', '$parts[2]', '$parts[3]', ....");

Example:

INSERT INTO people VALUES(NULL, "Firstname", "Lastname", ""123 Anywhere Address"", ...");
INSERT INTO people VALUES(NULL, "Firstname", "Lastname", '123 O'Walley Street', ...");

See the problem? I can't use either quoting consistently due to the nature of the data I'm
working with.

--
Andrew Gaffney
Network Administrator
Skyline Aeronautics, LLC.
636-357-1548

Search Discussions

  • WC -Sx- Jones at Mar 20, 2004 at 5:58 am

    Andrew Gaffney wrote:

    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", ""123 Anywhere
    Address"", ...");
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", '123 O'Walley
    Street', ...");

    See the problem? I can't use either quoting consistently due to the
    nature of the data I'm working with.
    Clean the data before saving it?

    s,^["'],,; # Kill Starting Quotes
    s,["']$,,; # Kill Ending Quotes

    Or convert them to a storable
    character: | for " and ~ for '

    HTH/Bill
  • Wiggins d'Anconia at Mar 20, 2004 at 4:07 pm

    WC -Sx- Jones wrote:
    Andrew Gaffney wrote:
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", ""123
    Anywhere Address"", ...");
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", '123 O'Walley
    Street', ...");

    See the problem? I can't use either quoting consistently due to the
    nature of the data I'm working with.

    Clean the data before saving it?

    s,^["'],,; # Kill Starting Quotes
    s,["']$,,; # Kill Ending Quotes

    Or convert them to a storable
    character: | for " and ~ for '
    Yikes, keep that away from my data....

    ...bind/execute or quote, good...

    http://danconia.org
  • Andrew Gaffney at Mar 20, 2004 at 5:31 pm

    WC -Sx- Jones wrote:
    Andrew Gaffney wrote:
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", ""123
    Anywhere Address"", ...");
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", '123 O'Walley
    Street', ...");

    See the problem? I can't use either quoting consistently due to the
    nature of the data I'm working with.

    Clean the data before saving it?

    s,^["'],,; # Kill Starting Quotes
    s,["']$,,; # Kill Ending Quotes

    Or convert them to a storable
    character: | for " and ~ for '
    I ended up stripping double quotes (s/\"//g) and escaping single quotes (s/\'/\\'/g). This
    seems to work for me.

    --
    Andrew Gaffney
    Network Administrator
    Skyline Aeronautics, LLC.
    636-357-1548
  • Wiggins d'Anconia at Mar 20, 2004 at 7:42 pm

    Andrew Gaffney wrote:
    WC -Sx- Jones wrote:
    Andrew Gaffney wrote:
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", ""123
    Anywhere Address"", ...");
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", '123
    O'Walley Street', ...");

    See the problem? I can't use either quoting consistently due to the
    nature of the data I'm working with.


    Clean the data before saving it?

    s,^["'],,; # Kill Starting Quotes
    s,["']$,,; # Kill Ending Quotes

    Or convert them to a storable
    character: | for " and ~ for '

    I ended up stripping double quotes (s/\"//g) and escaping single quotes
    (s/\'/\\'/g). This seems to work for me.
    This is a lazy hack and a bad habit to get into, IMO. DBI provides
    various useful ways to allow your data to not be corrupted (aka changed)
    while allowing your code to be cross platform and cross DB compatible.

    Do what you will, but this is one of the first things I would clean up
    when arriving on a project that uses this "fix".

    http://danconia.org
  • Andrew Gaffney at Mar 20, 2004 at 11:22 pm

    Wiggins d'Anconia wrote:
    Andrew Gaffney wrote:
    WC -Sx- Jones wrote:
    Andrew Gaffney wrote:
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", ""123
    Anywhere Address"", ...");
    INSERT INTO people VALUES(NULL, "Firstname", "Lastname", '123
    O'Walley Street', ...");

    See the problem? I can't use either quoting consistently due to the
    nature of the data I'm working with.



    Clean the data before saving it?

    s,^["'],,; # Kill Starting Quotes
    s,["']$,,; # Kill Ending Quotes

    Or convert them to a storable
    character: | for " and ~ for '


    I ended up stripping double quotes (s/\"//g) and escaping single
    quotes (s/\'/\\'/g). This seems to work for me.
    This is a lazy hack and a bad habit to get into, IMO. DBI provides
    various useful ways to allow your data to not be corrupted (aka changed)
    while allowing your code to be cross platform and cross DB compatible.

    Do what you will, but this is one of the first things I would clean up
    when arriving on a project that uses this "fix".
    This is a one time "fix". I writing a web-based client directory for a lawyer. He
    currently keeps all his client contact information in a 137 page Word document with a
    table. I'm using Word and Excel to get the data in a tab delimeted form to pull apart with
    Perl and insert into the DB. The data is in horrible shape and I'm just doing whatever I
    can to get it into the DB.

    --
    Andrew Gaffney
    Network Administrator
    Skyline Aeronautics, LLC.
    636-357-1548
  • Oliver Schnarchendorf at Mar 20, 2004 at 6:10 am

    On Fri, 19 Mar 2004 23:21:20 -0600, Andrew Gaffney wrote:
    See the problem? I can't use either quoting consistently due to the
    nature of the data I'm working with.
    How about using the power that is offered to you by Perl??? There is a function offered by the DBI module called 'quote'.

    Following is a copy and paste from the DBI-docs. Btw. 'perldoc DBI' on your local terminal and a bit of searching could have saved you a lot of time :)

    /oliver/

    --- cnp ---
    quote

    $sql = $dbh->quote($value);
    $sql = $dbh->quote($value, $data_type);


    Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

    $sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
    $dbh->quote("Don't");


    For most database types, quote would return 'Don''t' (including the outer quotation marks).


    An undefined $value value will be returned as the string NULL (without single quotation marks) to match how NULLs are represented in SQL.


    If $data_type is supplied, it is used to try to determine the required quoting behaviour by using the information returned by "type_info". As a special case, the standard numeric types are optimized to return $value without calling type_info.


    Quote will probably not be able to deal with all possible input (such as binary data or data containing newlines), and is not related in any way with escaping or quoting shell meta-characters.


    It is valid for the quote() method to return an SQL expression that evaluates to the desired string. For example:

    $quoted = $dbh->quote("one\ntwo\0three")


    may return something like:

    CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')


    The quote() method should not be used with "Placeholders and Bind Values".

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupbeginners @
categoriesperl
postedMar 20, '04 at 5:22a
activeMar 20, '04 at 11:22p
posts7
users4
websiteperl.org

People

Translate

site design / logo © 2022 Grokbase