CODEWITHSUNDEEP
perlsqlite
Sonal
Sonal

Reputation: 43

Having trouble in saving scalar variable into DB (sqlite3)?

# I am saving output to an array and the array looks like this:-

60=20130624-09:45:02.046|21=1|38=565|52=20130624-09:45:02.046|35=D|10=085|40=1|9=205|100=MBTX|49=11342|553=2453|34=388|1=30532|43=Y|55=4323|54=1|56=MBT|11=584|59=0|114=Y|8=FIX.4.4|

# Then i converted this array to scalar variable like this:-

$scal=join('' , @arr);

# And now I am trying to save this into db:-

my $st = qq(INSERT INTO demo (fix)
      VALUES ($scal));
my $r = $dbh->do($st) or die $DBI::errstr;

#And my table schema is:-

CREATE TABLE demo (fix varchar);

And I keep getting errors :- DBD::SQLite::db do failed: near ":45": syntax error at pdb.pl line 92, <STDIN> line 1.
DBD::SQLite::db do failed: near ":45": syntax error at pdb.pl line 92, <STDIN> line 1.

Any help will be appreicated

Upvotes: 2

Views: 114

Answers (1)

PerlDuck
PerlDuck

Reputation: 5730

The way you denote your array is a bit weird. Usually you would write it as

my @arr = ( '60=20130624-09:45:02.046',
            '21=1',
            '38=565',
            ... );

or whatever your actual content is. But this is not the problem here because you flatten it to the string $scal anyway.

One way to insert this string into your DB is to put ticks (') around it:

my $st = qq(INSERT INTO demo (fix) VALUES ('$scal'));
my $r = $dbh->do($st) or die $DBI::errstr;

But this is bad because it's vulnerable to SQL injection (http://imgs.xkcd.com/comics/exploits_of_a_mom.png). Consider the case your string is foo'); delete from demo; --. The final result would then be

INSERT INTO demo (fix) VALUES ('foo'); delete from demo; --')

The second reason why this is bad: Your string could contain ticks ($scal="foo's bar") and that also would mess up the resulting INSERT statement:

INSERT INTO demo (fix) VALUES ('foo's bar');

Conclusion: it's always better to use parameterized queries:

my $st = 'INSERT INTO demo (fix) VALUES (?)';
my $r = $dbh->do($st, undef, $scal) or die $DBI::errstr;

The undef is for additional SQL options (I've rarely seen anything different from undef here). The following parameters are replaced for the ?s in the statement. The DB driver does all the quoting for you. The more ? you use, the more parameters you must supply to do():

my $st = 'INSERT INTO sample_tbl (col1, col2, col3) VALUES (?, ?, ?)';
my $r = $dbh->do($st, undef, 'foo', 42, $scal) or die $DBI::errstr;

Upvotes: 3

Related Questions