Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts

    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #2
    Regular Coder
    Join Date
    Oct 2004
    Posts
    168
    Thanks
    0
    Thanked 5 Times in 5 Posts
    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.

    Code:
    my $sth = $dbh->prepare("INSERT INTO my_table (id, name) values (?, ?)");
    
    $sth->execute($id, $name);
    Last edited by Puffin the Erb; 02-02-2010 at 12:26 PM.

  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #4
    Regular Coder
    Join Date
    Oct 2004
    Posts
    168
    Thanks
    0
    Thanked 5 Times in 5 Posts
    Bound values will be treated as individual scalars so 'dangerous' characters become literal in effect.

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    ..... so 'dangerous' characters become literal in effect.
    And that means they are safe??

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #6
    Regular Coder
    Join Date
    Oct 2004
    Posts
    168
    Thanks
    0
    Thanked 5 Times in 5 Posts
    Safer, nothing is 100% safe ;-)

  • #7
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Thank you.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •