Replace one placeholder for another placeholder in Java String-CodePudding

Let's say we have the following String:

"SELECT * FROM table WHERE field1 = :field1Param AND field2 IN (:field2Param)"

I want to replace the placeholder :someName for $P{someName}, that means the above String would become:

"SELECT * FROM table WHERE field1 = $P{field1Param} AND field2 IN ($P{field2Param})"

What's the best approach to do this in Java? I implemented my own manual solution, but it looks big and ugly. I have the feeling there is a better way to do it.

CodePudding user response：

What you want is very very difficult. After all, SQL is not actually regular. You'd have to write an SQL parser which is not easy. For example, SELECT * FROM foo WHERE x IN (':field1ParamHello'), should that be replaced, or not?

More generally, why do you want to do this? Allowing tainted (as in, from a source you don't implicitly trust) into an SQL query like this is a massive security hole. The only safe way to do this is using a PreparedStatement, and then setting parameters using e.g .setString().

There are abstractions built on top of this, and they even use placeholders. Libraries like JOOQ and JDBI. Those are safe ways to do this. Trying to parse the SQL (or even a solution that doesn't seem like it could possibly work, such as using a regexp, as SQL isn't regular) sounds like you're writing code that works great... until a year from now when all of a sudden your systems are now owned by some enterprising hacker and somebody gets fired or worse.

CodePudding user response：

This looks like a case for replaceAll with regex for me which could look something like this.

public class Main {
    public static void main(String[] args) {
        String sql = "SELECT * FROM table WHERE field1 = :field1Param AND field2 IN (:field2Param)";

        sql = sql.replaceAll(":([a-zA-Z0-9] )", "\\$P{$1}");

        System.out.println(sql);
    }
}

Do take a look at this