CODEWITHSUNDEEP
mysqlperl
mopsyd
mopsyd

Reputation: 1922

How to escape '\n' in Perl for a MySQL query?

I am writing a Perl script to automatically upload an Excel document into a MySQL database table using Automator in OSX. I have already written a functional query:

load data
local infile '/pathToFile/file.csv'
replace into table database.table
fields terminated by ','
enclosed by '"'
lines terminated by '\r'
ignore 1 lines;

The problem is my Perl script is not functioning because of a conflict with the \ character. I am kind of a Perl newbie, so I don't know how to escape this character correctly to make the query work.

My Perl script:

#!/usr/bin/perl

# PERL MODULE
use Mysql;

# HTTP HEADER
print "Content-type: text/html \n\n";

# Set Variables
$host = "127.0.0.1";
$database = "database";
$tablename = "plugin_status";
$user = "user";
$pw = "password";

# PERL MySQL Connector
$connect = Mysql->connect($host, $database, $user, $pw);

# Run Query to update table
$myquery = "load data local infile '/pathToFile/file.csv' replace into table database.table fields terminated by ',' enclosed by '"' lines terminated by '\r' ignore 1 lines;";

# EXECUTE THE QUERY FUNCTION
$execute = $connect->query($myquery);

Here is the error generated:

String found where operator expected at perlfile.pl line 20, near ""load data local infile '/pathToFile/file.csv' replace into table database.table fields terminated by ',' enclosed by '"' lines terminated by '"
    (Missing operator before ' lines terminated by '?)
Backslash found where operator expected at perlfile.pl line 20, near "' lines terminated by '\"
    (Missing operator before \?)
syntax error at perlfile.pl line 20, near ""load data local infile '/pathToFile/file.csv' replace into table database.table fields terminated by ',' enclosed by '"' lines terminated by '"
Bad name after r' at perlfile.pl line 20.

Upvotes: 2

Views: 956

Answers (3)

Marcel Kraan
Marcel Kraan

Reputation: 107

I prefer to let mysql quote my string Like a textarea message:

my $message = $dbh->quote($in{'message'});

Upvotes: 0

Filip Roséen
Filip Roséen

Reputation: 63807

if you want the resulting string to contain the two-byte-sequence \r you will need to escape the backslash when wrapping your string in-between ".

Also remember that your string cannot contain any free " if you use " to wrap the contents of your string, if this is the case you will need to escape every " that you want to be a part of your string.. see the below example.

my $data = "hello, I can write \"quotes\" inside my string";

Though there are much simpler alternatives so that you don't have to worry about escaping anything1.

$myquery = q{
  load data local infile '/pathToFile/file.csv'
  replace into table database.table
  fields
    terminated by ','
    enclosed by '"'
    lines terminated by '\r'
  ignore 1 lines;
};

1. Unless you plan on using } in your string..

Read more about strings and escape sequences here:

Upvotes: 9

Josh Y.
Josh Y.

Reputation: 876

First, you really need to use strict; and use warnings;. No exceptions! Anyway:

…enclosed by '"'…

As noted by refp, perl stops parsing your double-quoted string there. Either excape that double-quote with a backslash, or better, wrap it in q{} or a here-doc instead of double-quotes. 

#!/usr/bin/perl
use strict;
use warnings;

# this
my $query = q{
  load data local infile '/pathToFile/file.csv'
    replace into table database.table
      fields terminated by ','
      enclosed by '"'
      lines terminated by '\r'
      ignore 1 lines;
};
# or this
my $query = <<'SQL';
  load data local infile '/pathToFile/file.csv'
    replace into table database.table
      fields terminated by ','
      enclosed by '"'
      lines terminated by '\r'
      ignore 1 lines;
SQL

Upvotes: 4

Related Questions