Hacker News Reader

Comment by tomp | Hacker News Reader

I believe that most databases are secure, especially the open source ones.

What you should be careful about is the things surrounding the database: the .php files (or whatever) that read/write the database, and the system it is running on.

Basic security practice for the web: NEVER trust user input: check and recheck all the GET/POST variables, check that numbers are numbers, that strings are correct strings (they have no funny characters, such as " or ; (for databases) or <>"&' (for HTML) or . (for paths)). Check all input into the databases (to prevent SQL injections) and all output for to the user (for XSS).

Basic security practice for sysadmins: Use up-to-date OS and software. Use strong passwords. Almost never run root. Make remote access hard.

This seems easy, and for the most part, it is. It's just so many things that people forget to check for them all.

View on HN

Yes, let's secure our databases against O'Reillys and AT&Ts submitting their funny names! <g>

It's not characters that get you, it's lack of escaping or escaping for the wrong context (e.g. magic_quotes won't work for HTML)

• For SQL use prepared statements exclusively (never let "oh, it's just a number so I don't need to" fool you)

• Escaping doesn't differ between "trusted" and "untrusted" data (and these boundaries are too easy to break eventually).

Just escape everything, always. In PHP it means every `echo $var` is a likely vulnerability and `echo htmlspecialchars($var, ENT_QUOTES)` (in HTML except script) or `json_encode($var)` (in script) is a must.

Obviously, you should do defense in depth, so input validation is great and some filtering just-in-case may be warranted, but escaping alone (assuming done well) is sufficient for security, while filtering alone is not.

> For SQL use prepared statements exclusively (never let "oh, it's just a number so I don't need to" fool you)

I cannot vote this up enough. Also, depending on what database you are using (eg Oracle) if you don't use prepared statements (aka bind variables) you are guarantee killing your DB performance.

People have argued with me in the past that for things like sorting the data they cannot use bind variables. In that case, use the user input to select which safe string to use, eg:

    if user_select_sort == 'by_account_num'
      return 'order by account_num asc'
    elsif user_select_sort == 'by_transaction_date'
      return 'order by transaction_date'
    else 
      return ''
    end if

Then if someone sends in something tricky, it will just order wrong.

On the other hand some uses of bind variables can kill performance: http://www.dbspecialists.com/specialists/specialist2003-11.h...

Well, yes, but only when your data is skewed in general. Tom Kyte gives a 1 - 2 hour presentation about bind variables, bind variable peeking, overbinding, SQL Injection, parsing etc - great stuff if you are an Oracle guy and can get to one of this seminars.

The way I write software, such values of user_select_sort would never even be possible... It's much slower to compare strings than to compare numbers, and passing long descriptive values that are actually booleans or short enums is just a waste of bandwidth (assuming they are passed as GET/POST variables).

Why not just pass numbers instead?

Numbers or strings wasn't the point really. You can do 'order by 1' or 'order by 2' in SQL to order by the first or second selected col etc, but if you used used the number passed directly from the user in the SQL statement, you are open to SQL injection. Feel free to use the number in a case statement to select the order by string to concat into your SQL however.

Actually, it's not easy. It's incredibly difficult to have a webapp with a wide range of functionality that doesn't leak data to SQL injections. There's plenty of stuff that can get past the precautions you listed[1][2], although pornel is closer to the mark.

[1]http://ha.ckers.org/sqlinjection/

[2]https://docs.google.com/viewer?url=http://www.ihteam.net/pap...

Another obvious weak point is not controlling access to copies of production databases. Developers getting access to copies of production databases full of personal info is terrifying, and yet not uncommon.

Reply on news.ycombinator.com