4 ms·
> 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
by fendale 15y ago
> 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.
- lmz 15y agoOn the other hand some uses of bind variables can kill performance: http://www.dbspecialists.com/specialists/specialist2003-11.html http://www.dbspecialists.com/specialists/specialist2003-11.h...
- fendale 15y agoWell, 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.
- tomp 15y agoThe 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?
- fendale 15y agoNumbers 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.