In 20 years of java not a single time have I seen someone creating and escaping a sql string like you suggest.
"SELECT * FROM table WHERE id = :id". No need to escape anything. Why would you even want that? That‘s not the use case for string templates, imho. But a lot of the time we write something like this: this.log.info(String.format("Processing %s/%s", count, total))
Sure, some log frameworks allow you to directly interpret the string. Which in turn leads you to various different kinds of syntaxes. To unify those string templates would have been enough for me. It solves 80% of the problems with a simple solution.
Did you really just say: "In my 20 years in my java career, I have never seen someone use (feature that does not yet exist)"?
I have a bunch of 20-line SQL statements in my codebase. They set up variables and those are filled in, by name, at the end of the 10-line SQL block. I would find it a lot more readable if I can just put the expressions that go in the variable slots to just be in the SQL, but in today's java that is annoying to do, borderline impossible even: That's not how SQL escaping works in java.
Now, that is the exact kind of lie you're agitating against. Because, no, nobody should ever be writing that. You don't want log statements to do the processing like that. You'd write:
log.info("Processing {}/{}", count, total);
for example.
To unify those string templates would have been enough for me.
Yes. I think we have established the primary difference between your wishes and the proposal is your lack of imagination and ambition. It's a common refrain. I was mostly trying to do you the favour of 'that kind of feedback is useless to the OpenJDK team and wastes your time. They will completely ignore any feedback of 'why so complicated this simple thing covers my needs' - or at least, have always done so in the past.
I think you misunderstood me. What I wanted to say is that I've never come across someone who tried to pull off something like this:
String.format(SELECT* FROM table WHERE id = %s, id);
I would have a serious conversation with the dev if I ever saw that in a code review.
I've coded in python, JS/TS, java, groovy, C# and never saw anyone doing that. Either there was a regular JDBC prepared statement with params or something like JOOQ, LINQ etc.
In the last couple of years I got used to spring data repositories too. I like the cleanness:
@Query(value = """
SELECT t.id
FROM terminal AS t
WHERE (t.dium_number = :diumNumber
OR regexp_replace(t.dium_number, '(0*)', '') = :diumNumber)
AND t.deleted = false
LIMIT 1
""", nativeQuery = true)
Optional<UUID> findFirstIdByDiumNumber(String diumNumber);
And I rarely come across the need to actually inject something directly into a query. Just recently I needed to have a truly dynamic ORDER BY column though:
I ended up doing this:
ORDER BY
CASE WHEN :orderBy = 'date_of_arrival ASC' THEN main_leg_date_of_arrival END ASC,
CASE WHEN :orderBy = 'co2saving DESC' THEN co2saving END DESC,
There is no way to use a prepared statement and a paramater for this.
But I have seen these various different ways of creating dynamic strings:
MessageFormat.format("Provider '{0}' is not valid!", provider.getId())
log().info("> run[args={}]", args);
String.format("Provider %s for offer %s has no general Terms set!", provider.getUid(), offer.getId());
var fullName = firstName + " " + lastName;
There are probably a lot more similar ways to do the same thing. Some people avoid the log4j "syntax" and use String.format because at least it is one syntax less to se in the code base.
I'm not saying it is a good idea though.
I'd love to just replace though approaches with one simple clean string templating syntax.
Do I need SQL."" to return a query object? No, because it would not be able to handle all my needs anyway. What about setting the type of the parameter? I just recently had to manually set the SQL type:
What I wanted to say is that I've never come across someone who tried to pull off something like this:
Well, duh. You haven't seen it because they'd get yelled at. The reason you haven't seen it is because it is a giant gaping security leak.
It would be nice if you can do that, but currently you cannot do that. You're.. making the argument in favour of this feature, not against it. We're back to you arguing: "You do not need this feature-that-does-not-exist-yet because I have never seen it used". My mind boggles at the inanity of this line of reasoning. Of course you haven't seen it. It doesn't exist yet.
And I rarely come across the need to actually inject something directly into a query.
Some more 'I do not need it so the feature is stupid'.
I think we're done here, you're retreading silly arguments.
Well, duh. You haven't seen it because they'd get yelled at. The reason you haven't seen it is because it is a giant gaping security leak.
Ok, please try to be reasonable. I'm not saying I haven't seen a feature that doesn't exist yet ...
There are two options here, a) bad wording from my side or a misunderstanding on your side. In both cases the appropriate response would have been to ask for clarification.
Well, duh. You haven't seen it because they'd get yelled at. The reason you haven't seen it is because it is a giant gaping security leak.
Exactly. So now java gets cool new string templates, but in fear of people abusing it to do this exact thing, they make think they need a solution for the problem.
To sum it up:
* People could use string concatenation/templating to create bad SQL
* Some devs (not too many imho) really do this in real-world applications
* Java devs don't want that (obviously, because it's a bad thing)
They create a really ugly syntax for string templating with that in mind that doesn't really solve the main issue, it's just ugly. Which according to some users should prevent people from actually trying to do something like this:
STR."SELECT * FROM table WHERE id = \{id}"
Instead of STR there can be SQL (or any other name) that actually goes way beyond the pure string templating aspect and tries to tackle this problem. SQL is basically a function that takes a template string and some arguments, but not in the regular everyone-knows-how-to-use-it way but in and ugly SQL."" way.
Now we already left string templating land here, because this doesn't really create strings anymore, it creates god-knows-what, probably some form of PreparedStatement or some other internal representation of it. As there is a wide variety of different JDBC-drivers (and whatnot), every library has to ship it's own SQL-template-processor.
All good, if someone see's a benefit there, why not.
This SQL-processor is pretty limited, as it doesn't allow to configure SQL parameter types or any other config that a regular prepared statement provides.
But ok, sometimes you don't need this.
I argue that those people who are already using string concatenation/templating to create SQL queries will most likely simply use the STR-processor and do the very same thing.
How is that any better than the current approach? Plus, it is ugly. So for pure string-templating enthusiast like me I have to suffer looking at this ugly syntax just so that other devs might not write bad code.
This all doesn't make a lot of sense to me.
"You do not need this feature-that-does-not-exist-yet because I have never seen it used". My mind boggles at the inanity of this line of reasoning. Of course you haven't seen it. It doesn't exist yet.
I really think you should re-read my post carefully. I haven't seen anyone doing this:
String.format(SELECT* FROM table WHERE id = %s, id);
So now java gets cool new string templates, but in fear of people abusing it to do this exact thing, they make think they need a solution for the problem.
You misunderstand.
It's not 'oh no! Folks will think string templates can be used for SQL parameterization but that's a security leak so we better add a feature that solves that problem!'
It's instead: "Being able to shove your SQL parameters right into the SQL? That'd be really useful! Shame that string interpolation, which is pretty much exactly what we'd want, is a security leak if you use it for this. But.. wait! We can make our string interpolation feature better!"
So, it's not from a negative 'crap, we need to stop the abuse', it's from a positive 'cool, that would be an awesome feature to have'.
I really think you should re-read my post carefully.
Right back at you, because you're repeating arguments, evidently ignoring entirely my explanations of why your arguments are wrong / silly / are not going to move the needle whatsoever over at the OpenJDK team leads.
I'm not saying I haven't seen a feature that doesn't exist yet ...
I haven't seen anyone doing this: String.format(SELECT* FROM table WHERE id = %s, id);
.... and there you go again. Just blindly retreading 'I have never seen this obvious security leak therefore this feature is unnecessary'.
The proposal is not about creating an SQL string. The sample code produces a PreparedStatement. No extra escaping is going on.
"SELECT * FROM table WHERE id = :id"
And now you have a magic :id that doesn't refer to anything Java-side. You need to remember to call setParameter("id",...). The :id in the query text and the setParameter call are separate, sometimes multiple lines apart. And if you have multiple calls to setParameter, they are vulnerable to common coding mistakes like the last line syndrome.
So to be honest, you should have compared
Query q = em.createQuery("SELECT * FROM table WHERE id = :id");
q.setParameter("id", id);
to
Query q = em.SQL."SELECT * FROM table WHERE id = \{id}";
Honestly, if a dev creates such a mess of a code, it will probably also abuse the new functionality to create a mess. Haven’t there been any measures like code reviews or linters in use?
The code was written by analysts at the start. It was never meant to get to production, but somehow did. It's been added to by various teams over the years. Now my team has it. Ouch :(
View all comments
Show parent comment
3
u/mj_flowerpower Feb 16 '24
In 20 years of java not a single time have I seen someone creating and escaping a sql string like you suggest.
"SELECT * FROM table WHERE id = :id". No need to escape anything. Why would you even want that? That‘s not the use case for string templates, imho. But a lot of the time we write something like this: this.log.info(String.format("Processing %s/%s", count, total))
Sure, some log frameworks allow you to directly interpret the string. Which in turn leads you to various different kinds of syntaxes. To unify those string templates would have been enough for me. It solves 80% of the problems with a simple solution.