SPUG: inserting content into mysql database
Peter Darley
pdarley at kinesis-cem.com
Tue Oct 3 12:09:05 PDT 2006
JD,
The simplest problem comes when there are multiple escape characters for
the quotes. For example, if I have a string that looks like: test\';select
4; and the apostrophes get doubled, it becomes test\'';select 4; and when
the quotes are added it becomes 'test\'';select 4;', and this is what I get
in my database I see:
neo=# select 'test\'';select 4;';
?column?
----------
test'
(1 row)
?column?
----------
4
(1 row)
So, as you see if I replaced ;select 4; with ;delete from my_table;, I
would be able to delete everything from my_table. Similarly I could insert
stuff into the db, etc.
There are probably other chances for sql injection with this weak
quoting as well.
Thanks,
Peter
-----Original Message-----
From: JD Brennan [mailto:jazzdev at gmail.com]
Sent: Tuesday, October 03, 2006 11:09 AM
To: Peter Darley
Subject: Re: SPUG: inserting content into mysql database
I agree that using a quote function specific to your DB
is the best. I think it is useful to know what's going on under
the covers though, which is why I mentioned that it's
doubling the single quotes.
What SQL injection attacks are you still open to if you
quote the single quotes? I thought that would close that
hole.
JD
On 10/3/06, Peter Darley < pdarley at kinesis-cem.com> wrote:
JD,
The problem with this is that it leaves you open to other types of
SQL injection, which may be an issue or not, depending on where the data is
coming from. Also, different dbs have different ways of escaping quotes, so
formatting your data by hand loses something in terms of portability.
I'd strongly suggest using the quote function if it's good enough in
MySQL (which I don't have an opinion about), or use place holders if the
quote isn't strong enough.
Thanks,
Peter Darley
-----Original Message-----
From: spug-list-bounces+pdarley=kinesis-cem.com at pm.org
[mailto:spug-list-bounces+pdarley=kinesis-cem.com at pm.org]On Behalf Of JD
Brennan
Sent: Tuesday, October 03, 2006 10:46 AM
To: spug-list at pm.org
Subject: Re: SPUG: inserting content into mysql database
In SQL you have to double the single quotes.
update FOO set X = 'This couldn''t happen to you'
of course there'a method in Perl to do it, as Keith
mentioned.
JD
On 10/3/06, Keith Reed <keith.reed at philips.com> wrote:
Check out dbh->quote()
Keith
"luis medrano" <lmzaldivar at gmail.com >
Sent by:
spug-list-bounces+keith.reed= philips.com at pm.org
2006-10-03 10:35 AM
To spug-list at pm.org
cc
Subject SPUG: inserting content into mysql database
Classification
List,
I running this code feeding a database:
my $sth1=$dbh->prepare("INSERT INTO wp_posts(post_author, post_date,
post_date_gmt, post_content,post_title, post_status, comment_status,
ping_status,post_name, post_modified, post_modified_gmt,guid)
VALUES('$post_author','$post_date','$post_date_gmt','@post_content','$post_t
itle','$post_status','$comment_status','$ping_status','$post_name','$post_mo
dified','$post_modified_gmt','$guid')") or die; # "Couldnt prepare
statement: " . dbh->errstr;
my $rv1 = $sth1->execute();
but my problem is if any of the values of @post_content or
$post_title contain apostrophe the script show this error not executing of
feeding the database:
DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near 's%20secret%20visits%20to%20heiress/article.do">Goldsmith
s secret visits to heir ' at line 2 at posting-news.pl
anybody knows how can I fix this without removing the apostrophe?
Thanks,
Luis_____________________________________________________________
Seattle Perl Users Group Mailing List
POST TO: spug-list at pm.org
SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
MEETINGS: 3rd Tuesdays
WEB PAGE: http://seattleperl.org/
_____________________________________________________________
Seattle Perl Users Group Mailing List
POST TO: spug-list at pm.org
SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
MEETINGS: 3rd Tuesdays
WEB PAGE: http://seattleperl.org/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.pm.org/pipermail/spug-list/attachments/20061003/2a64274f/attachment.html
More information about the spug-list
mailing list