0

How would I use string.format() to omit the + string concatenation + in this Java/SQL code.

String sql = "SELECT * FROM "+p_tableName+" WHERE 1 = 0";
Rohit Jain
  • 209,639
  • 45
  • 409
  • 525
Croeber
  • 451
  • 3
  • 10
  • 22
  • Your condition is surprising. `(1 = 0)`? Anyways, what have you tried? – Rohit Jain Dec 04 '12 at 18:39
  • The 1=0 is odd, but it's not too uncommon. I've seen it used a lot where for some reason you want to execute but don't want results. Usually it's a test thing. – David Dec 04 '12 at 18:44

2 Answers2

1
String sql = String.format("SELECT * FROM %s WHERE 1 = 0", p_tableName);

Always be careful when making SQL statements this way as they can very easily be used against you in SQL injection attacks.

Kevin DiTraglia
  • 25,746
  • 19
  • 92
  • 138
  • For any String.format commands you must escape out any existing % symbols. These symbols are often used in LIKE operations. – Croeber Dec 04 '12 at 18:43
  • How would I "ESCAPE" out of the % What does that mean? – Croeber Dec 04 '12 at 18:44
  • Would this escape be more applicable here. – Croeber Dec 04 '12 at 18:44
  • select count(userfieldid) from tr_userfield where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'" – Croeber Dec 04 '12 at 18:45
  • @user1865053 In java `%` is escaped by writing: `%%` – Kevin DiTraglia Dec 04 '12 at 18:48
  • So use String sql = String.format("SELECT * FROM %%s WHERE 1 = 0", p_tableName); – Croeber Dec 04 '12 at 18:51
  • @user1865053 no what I listed above is correct, %s will be replaced by the string p_tableName, if you wanted a % in the actual query such as `WHERE Column1 like '%%test%%'` you would use 2 %'s since you actually want the % character in the query that case. – Kevin DiTraglia Dec 04 '12 at 18:52
  • So is this what he is talking about; to use string.format() to omit the + string concatenation + in this Java/SQL code and then in any String.format commands you must escape out any existing % symbols........ String sql = String.format("SELECT last_name FROM %s WHERE last_name LIKE '%% %s %%' ", p_tableName, match);....... – Croeber Dec 04 '12 at 19:19
  • @user1865053 looks about right, but again be careful if the user has any control over what is in your 2 variables or you might be an easy target for SQL injection. – Kevin DiTraglia Dec 04 '12 at 20:38
1

You can use String.format("SELECT * FROM %s WHERE 1 = 0", table_name). But, I strongly advise you to use PreparedStatement instead (See How do I make a prepared statement?, https://stackoverflow.com/a/396765/130224, PreparedStatements and performance, and Using Prepared Statements). PreparedStatement yields higher performance and security.

Community
  • 1
  • 1
reprogrammer
  • 14,298
  • 16
  • 57
  • 93
  • I am performing a data migration and moving an embedded SQL statement into a static constant then JUnit testing it to compare the migration of the data against a source and target database. – Croeber Dec 04 '12 at 18:49
  • He can't use a PreparedStatement to fill in the table in his scenario. What other tool are available to do just that? – Damien Mar 13 '15 at 13:43
  • @Damien is correct. PreparedStatement doesn't allow control of the table name. This answer doesn't apply to the question. – brycem Oct 23 '17 at 03:16