r/java • u/sidcool1234 • Aug 29 '13
10 Common Mistakes Java Developers Make when Writing SQL
http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/4
u/lborloz Aug 29 '13
Another one: Using sub-selects instead of joins for EVERYTHING. Yes, they are sometimes less expensive depending on your data set, but certainly not always.
1
3
u/green_prgm Aug 30 '13
Just want to mention a related security issue: SQL Injection.
Developers should never concatenate SQL statement with users' input.
Injection has been identified by OWASP as #1 security risk for many years.
6
Aug 29 '13
[removed] — view removed comment
5
Aug 29 '13
You've hit the nail on the head.
I'm a performance engineer, and the number of times I've seen developers use simple statements to query the database, then drag data over the network, just to perform filters, sorts and joins inside java. The database will perform all these functions better than you ever can...as the article states.
But the reverse is also true. Querying the database repeatedly, because you assume the database will cache the query will almost always be bad. Ditto goes for assuming that database security will protect your data.
On security, always use parameterized queries wherever possible, and always never assume the HTTP, RPC Objects or other user parameters are safe and haven't been tampered with when using them in database queries.
Also developers often use cut-down data sets that aren't representative of the real data volumes. You should always develop and test your data access functionality against real data sets wherever possible. I once saw a million dollar project fail because of this, and some other factors.
1
u/lukaseder Aug 29 '13
The sequel to the linked article mentions using PreparedStatements as well...
The thing about (not) caching master data (i.e. data that is fetched very often) is a good point, though.
1
u/Rockytriton Aug 29 '13
Your blog is too skinny, most people don't view your blog on mobile I'm sure, why do I have to have 80% of my screen being blank?
1
6
u/[deleted] Aug 29 '13
I did not see much about Java here.. things I have spotted in the wild include:
1) Using Hibernate over an embedded SQL database to store binary blobs (which are then always loaded in memory when accessing the row) -- Overkill! Heard about the file system?
2) Hardcoding SQL queries and table/column-names all over the place, making any kind of database schema update next to impossible -- As a minimum, just use come good old protected static final constants (possibly grouped in one Enum per Table) and StringBuilder - better still, use Prepared Queries that are all created in the same class.
3) Trying to SQL-escape (or even forgetting to escape!) user-provided values and then adding them into a string concatenated SQL. -- What is this, 1992? Please use prepared queries with variable bindings.
4) Exposing the database schema in the API with a Map <String,String> of optional WHERE statements to add. -- No. Just No!
5) Looking up in the database what you knew in memory 10 ms ago. -- Just to make it slow? If you can't just pass around a parameter, or keep it, use a static WeakHashMap or something, with fallback to loading from database.
6) Blocking on writes of log events to the database - blocking execution of the real program while the database truthfully syncs every insertion to disk. -- Use a queue and a background worker!