Tech Off Thread

16 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL Injection - Best practices

Back to Forum: Tech Off
  • User profile image
    SlackmasterK

    A buddy of mine has recentnly been transferred to the web design and coding part of the company I work at (I took over his old job). He was watching the Code Room and it piqued his curiosity:  He tried that SQL injection on his own site and apparently it's prone.  Can anyone give me some pointers to forward to him regarding web design best practices to prevent SQL injection?

  • User profile image
    Sven Groot

    Simple: never concatenate strings to create SQL statements. Always use parameters.

  • User profile image
    DoomBringer

    Single quotes are killer.  Filter those out.

    That isn't the only way to do them, though.

    Another thing to watch out for is when someone puts raw HTML into something that will be shown to someone else at some point.  A really simple quiz web app would dump whatever you wrote back to the owner at some point.  So I put "</body>" into it.  I mean, I uh, I conjectured that would be possible.

  • User profile image
    Ang3lFir3

    Sven Groot wrote:
    

    Simple: never concatenate strings to create SQL statements. Always use parameters.


    QFT
    parameterize parameterize... parameterize.......

    btw i met the guy in that vegas vid (forget his name) at  VSlive! in San Francisco.... really cool guy...

    also consider the use of Stored procs if possible and only if they will truly help performance....

    you can even create a sub to look for things in the user input like "xp_" , "sp_" ,  " or 1=1 --"  and then record the attempts for security purposes

    always remember user input == evil

    also make sure to create a user with read only privaleges to the db and use that user to query info.... create another limited user to use when writing to the db.... never use the sa account..... ima say it again just in case...... never use the sa account!

  • User profile image
    opsan

    I got a bit of mail from SPI Dynamics, offering a whitepaper on SQL Injection.  I posted a link to it at http://blog.opsan.com/archive/2006/06/02/28647.aspx

  • User profile image
    littleguru

    Use parameters. It's impossible to inject by using them.

  • User profile image
    Manip

    Here is a post explaining using parameters to protect your server from injection based attacks.

  • User profile image
    blowdart

    littleguru wrote:
    

    Use parameters. It's impossible to inject by using them.



    Oh it certainly is. Heh. The problem would come if you were building dynamic sql inside the statement.

    For example take a typical search stored procedure, it would split a search string up by spaces, surround each word with % and split them up with an AND between. The only way to then search is to use that in conjunction with sp_executesql.

    And if you pass in a typical injection string, oops ....




  • User profile image
    W3bbo

    blowdart wrote:
    
    littleguru wrote:

    Use parameters. It's impossible to inject by using them.



    Oh it certainly is. Heh. The problem would come if you were building dynamic sql inside the statement.

    For example take a typical search stored procedure, it would split a search string up by spaces, surround each word with % and split them up with an AND between. The only way to then search is to use that in conjunction with sp_executesql.

    And if you pass in a typical injection string, oops ....


    Which reminds me, how well do C#-Sprocs make Dynamic SQL obsolete? (if at all)

  • User profile image
    Maurits

    blowdart wrote:
    Oh it certainly is. Heh. The problem would come if you were building dynamic sql inside the statement.


    Any time you're building a SQL statement from a string, you need to think about injection.  This is true whether you're building it in ASP.Net, building it in Perl, building it in SQL, building it in Javascript, etc., etc., etc.

  • User profile image
    blowdart

    Maurits wrote:
    
    blowdart wrote:Oh it certainly is. Heh. The problem would come if you were building dynamic sql inside the statement.


    Any time you're building a SQL statement from a string, you need to think about injection.  This is true whether you're building it in ASP.Net, building it in Perl, building it in SQL, building it in Javascript, etc., etc., etc.


    Of course, I was just trying to make sure that people don't believe that parameterised queries solve everything. They most certainly don't.

  • User profile image
    Ny-Mobster

    I program php and on my website I find it best prcatice to protect my variables with the php mysql_real_escape_string(); function, this adds backslashes to certain characters that are effective in SQL injections. I also find it best to restrict wildcards like % from user input, for example my login script restricts users from using non-alphanumeric characters in their usernames and passwords. So I will use a form to post the user input and restrict it like this -

    <?php
    $user = strip_tags($_POST['user']);
    $user =
    mysql_real_escape_string($user);
    $pass = strip_tags($_POST['pass']);
    $pass = mysql_real_escape_string($pass);

    if (ereg("[^A-Za-z0-9]", $user)){
    $error = "Username's can only contain alpha-numeric characters.";
    }elseif (!ereg("[^A-Za-z0-9]", $user)){


    if (ereg("[^A-Za-z0-9]", $pass)){
    $error = "Password's can only contain alpha-numeric characters.";
    }elseif (!ereg("[^A-Za-z0-9]", $pass)){

    rest of script here.

    }else{
    $error = "User input error.";
    }
    }else{
    $error = "User input error.";
    }

    if (isset($error)){
    print $error;
    }
    ?>


  • User profile image
    wisemx
  • User profile image
    blowdart

    Ny-Mobster wrote:
    
    if (ereg("[^A-Za-z0-9]", $pass)){
    $error = "Password's can only contain alpha-numeric characters.";
    }elseif (!ereg("[^A-Za-z0-9]", $pass)){




    What an incredibly bad idea. Instead of escaping the SQL properly you instead stop users using strong passwords.

  • User profile image
    W3bbo

    blowdart wrote:
    What an incredibly bad idea. Instead of escaping the SQL properly you instead stop users using strong passwords.


    I come accross a lot of websites and services which put restrictions on passwords, like maxlength and only alphabetic characters. Usually it's a surefire way of indicating they don't hash their passwords, so I use a unique PW for those sites.

  • User profile image
    odujosh

    Simply put use stored procs or an ORM. Validate everything at every step in the relay (each layer) that can modify a particulair entity. Client -> Business Object layer -> Data Access Layer -> to wherever. This is a fundamental to quality software. Specially but not limited to if you need to use query building in your stored proc.

    Limiting your mutable footprint may be a valid option. Knowing is most of the battle.

    Later you start planning for this issue the more costly the endeavor becomes. As most things in software egineering:)

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.