CODEWITHSUNDEEP
javastringjdbc-odbc
Sakura
Sakura

Reputation: 7

How to replace a string in a string with integer type in java?

I have a requirement. The technology is quite old doesn't support spring at all . It is pure java application with jdbc connection.

Requirement is : Suppose

select * from employee where empid = <<empid>> and designation = 'Doctor'

I am trying to replace <> with actual int value in java . How I can do it ?

String query = "select * from employee where empid = <<empid>> and designation = 'Doctor'";
if(query.contains("<<empid>>"))
/// Here I want to replace <<empid>> with actual int value in java

Any leads will be helpful

Upvotes: 0

Views: 338

Answers (1)

rzwitserloot
rzwitserloot

Reputation: 103668

The code you didn't paste, that actually executes the SQL is either [A] a massive security leak that needs serious rewrites, or [B] is using PreparedStatement.

Here's the problem: SQL injection. Creating the SQL string by mixing a template or a bunch of string constants together with a bunch of user input is a security leak. For example, if you try to make SELECT * FROM users WHERE email = '[email protected]' by e.g. String sql = "SELECT * FROM users WHERE email = '" + email + "'";, the problem is, what if the user puts in the web form, in the 'email' field: [email protected]'; DROP TABLE users CASCADE; EXEC 'FORMAT C: /y /force'; --? Then the SQL becomes:

SELECT * FROM users WHERE email = '[email protected]'; DROP TABLE users CASCADE; EXEC 'FORMAT C: /y /force'; --';

That is legal SQL and you really, really, really don't want your DB engine to execute it.

Each DB engine has its own ideas on what's actually legal, and may do crazy things such as treating curly quotes as real quotes, etc. So, there is no feasible blacklist or whitelist technology you can think of that will properly cover all the bases: You need to ask your DB engine to do this for you, you can't fix this hole yourself.

Java supports this, via java.sql.PreparedStatement. You instead always pass a fully constant SQL string to the engine, and then fill in the blanks, so to speak:

PreparedStatement ps = con.prepareStatement("SELECT * FROM users WHERE email = ?");
ps.setString(1, "[email protected]");
ps.query();

That's how you do it (and add try-with-resources just like you should already be doing here; statements and resultsets are resources you must always close). Even if you call .setString(1, "[email protected]'; DROP TABLE users CASCADE; --"), then it'll simply look for a row in the database that has that mouthful in the email field. It will not delete the entire users table. Security hole eliminated (and this is the only feasible way to eliminate it).

So, check out that code. Is it using preparedstatement? In that case, well, one way or another that code needs to be calling:

ps.setInt(1, 999);

Where ps is the PreparedStatement object created with connection.prepareStatement(...) where ... is either an SQL constant or at least your input string where the <<empid>> was replaced with a question mark and never with any string input from an untrusted source. The 1 in ps.setInt(1, 999) is the position of the question mark (1 = the first question becomes 999), and the 999 is your actual number. It may look like:

if (input instanceof String) {
    ps.setString(idx++, (String) input);
} else if (input instanceof Integer) {
    ps.setInt(idx++, ((Integer) input).intValue());
} ...

etcetera. If you don't see that, find the setInt invoke and figure out how to get there. If you don't see any setInt, then what you want is not possible without making some updates to this code.

If you don't even see PreparedStatement anywhere in the code, oh dear! Take that server offline right now, research if a security leak has occurred, if this server stored european data you have 72 hours to notify all users if it has or you can't reasonably figure out e.g. by inspecting logs that it hasn't, or you're in breach of the GDPR. Then rewrite that part using PreparedStatement to solve the problem.

Upvotes: 1

Related Questions