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.
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).
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.
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.
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.