preventing injections

Hi,

I have the following sort of insert statement and I want to be sure I understand the security stuff.
pseudo code
[code]
insert into my_table
set (col2, col3)
values ( 'paragraph_heading', 'a_paragraph' )
where business_id = '1'
[code]

The 2nd and 3rd cols are filled with user inputted data. the WHERE is provided by the session or a scripted query with no user input.

So, my question is... can an injection be done in the text column or just in the where (or AND ) clauses? I need to allow nealry all characters to be insertable for the paragraph (text format) but I wonder if that could leave it open to an injection attack. something tells me the WHERE and AND clauses are where the attack could come from.

bazz

All user input should be treated as a possible security issue and should be escaped appropriately depending on your database. Anywhere there is something variable in your statement should be looked at carefully.

I'm assuming two things here, you're using Perl and the DBI module.

If so, then appropriate escaping will be automatically applied if you bind your variables to the SQL statement using ? placeholders.


my $sth = $dbh->prepare("INSERT INTO my_table (id, name) values (?, ?)");

$sth->execute($id, $name);

Thank you for your reply.

I understand part of the escape subject but, what I don't seem to be able to read, is what would happen when someone may enter an extra command that could do damage ~ say by including a delete command. Are all the characters like ;/\:., etc changed to ascii alternatives by the placeholders, before putting the values into the db?

You have reminded my to watch for one of my many mistakes....previously I had ommitted to put the values into/through placeholders. I was only putting the variables in the WHERE & AND clauses in them. Why I got to that approach no-one - least of all me - could ever know :( Still, I haven't got too many scripts which input data - by far most are just retrievals of existing db data, so it shouldn't take too long to rectify.

Thanks again for your help so far.

bazz

Bound values will be treated as individual scalars so 'dangerous' characters become literal in effect.

..... so 'dangerous' characters become literal in effect.


And that means they are safe??

bazz

Safer, nothing is 100% safe ;-)

Thank you.

bazz