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

Question

# 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

Use ticks around $scal: INSERT ... VALUES ('$scal')); or (far!! better) use parameterized queries (those with the question marks in it). — PerlDuck
– PerlDuck, Commented Jan 1, 2017 at 16:24

PerlDuck · Accepted Answer · 2017-01-01 17:53:57Z

3

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;

answered Jan 1, 2017 at 17:53

PerlDuck

5,7463 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ikegami Over a year ago

(Accidental downvote using the app, which doesn't let me undo downvotes)

PerlDuck Over a year ago

@ikegami Never mind, things happen. And thank you for telling.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related