SQL variables in queries in Java

Question

I want to use several times the same values.

If I use in dbForge for MySQL next query,

SET @v1 = 123, @v2='2014-04-11', @v3 = 'user1', @v4='title1';
INSERT INTO test_table (TID, CREATED, OWNER, TITLE)
VALUES (@v1,@v2,@v3,@v4)
ON DUPLICATE KEY UPDATE
CREATED=@v2, OWNER=@v3, TITLE=@v4

it correctly executes, but in Java, when I use code

final String dbQuerry = "SET @v1 = %s, @v2='%s', @v3 = '%s', @v4='%s';\n"+
                          "INSERT INTO test_table (TID, CREATED, OWNER, TITLE)\n" + 
                          "VALUES (@v1,@v2,@v3,@v4)\n" +
                          "ON DUPLICATE KEY UPDATE\n" + 
                          "CREATED=@v2, OWNER=@v3, TITLE=@v4";

String currentQuerry = String.format(dbQuerry, t.getParam("ID"), 
                                               t.getParam("Date"),
                                               t.getParam("User"),
                                               t.getParam("Title"));
mDBStatement.execute(currentQuerry);

I have an exception

SQL Exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO test_table (TID, CREATED, OWNER, TITLE) VALUES (@v1,@v2,@v3,@v4) ON ' at line 2

I can use something like this

final String dbQuerry = "INSERT INTO test_table (TID, CREATED, OWNER, TITLE)\n" + 
                        "VALUES (?,?,?,?)\n" +
                        "ON DUPLICATE KEY UPDATE\n" + 
                        "CREATED=?, OWNER=?, TITLE=?";

  PreparedStatement st = mDBConnection.prepareStatement(dbQuerry);
  st.setInt(1, Integer.valueOf(t.getParam("ID")));
  st.setString(2, t.getParam("Date"));
  st.setString(5, t.getParam("Date"));
  st.setString(3, t.getParam("User"));
  st.setString(6, t.getParam("User"));
  st.setString(4, t.getParam("Title"));
  st.setString(7, t.getParam("Title"));

But it looks ugly.

Is there is a way to solve this problem?

spencer7593 · Accepted Answer · 2014-04-14 22:16:41Z

1

One option is to use the special VALUES() function to reference the value that would have been inserted into a column, if the INSERT had succeeded, like this:

...
ON DUPLICATE KEY
UPDATE CREATED = VALUES(CREATED)
     , OWNER   = VALUES(ONWER)
     , TITLE   = VALUES(TITLE)

The latter form in your example is preferred, using placeholders for the bind variables. What's ugly is having to supply the same value twice.

I'd recommend something like this:

final String dbQuerry = "INSERT INTO test_table (TID,CREATED,OWNER,TITLE)\n" + 
        " VALUES (?,?,?, ?)\n" +
        " ON DUPLICATE KEY UPDATE\n" + 
        " CREATED=VALUES(CREATED), OWNER=VALUES(OWNER), TITLE=VALUES(TITLE)";

PreparedStatement st = mDBConnection.prepareStatement(dbQuerry);
st.setInt(1, Integer.valueOf(t.getParam("ID")));
st.setString(2, t.getParam("Date"));
st.setString(3, t.getParam("User"));
st.setString(4, t.getParam("Title"));

And that's not ugly. That's the normative pattern.

Using the special VALUES() function is especially useful if we're upserting more than one row, either with a VALUES clause e.g.

INSERT INTO fee (fi, fo, fum)
VALUES
(1,'doo','dah'),(2,'menom','menah'),(3,'buhdeep','uhdeepee')
ON DUPLICATE KEY
UPDATE fo  = VALUES(fo)
     , fum = VALUES(fum)

Or, with an INSERT ... SELECT form:

INSERT INTO fee (fi, fo, fum)
SELECT t.ay, t.bee, t.cee FROM sometable t
ON DUPLICATE KEY
UPDATE fo  = VALUES(fo)
     , fum = VALUES(fum)

BTW... the error being returned from the first form is the type of error we'd expect if allowMultiQueries=true is not included in the connect string. Note that enabling multiple queries per execution effectively disables a security feature.

Consider carefully the SQL text that would be generated and sent to the database with some carefully crafted values:

val = "foo'; DROP TABLE students; --"

Using a prepared statement (with static SQL text with placeholder for bind variables, as in the example above) prevents this mode of SQL injection. And disallowing multiple statements in a single execution is another way to thwart SQL injection attacks.

edited Apr 14, 2014 at 22:16

answered Apr 14, 2014 at 15:38

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Woody Over a year ago

This seems the best way to do it. The other way (which is not safe) would be like be to use replaceAll("@v1", t.getParam("ID")) etc

Esperan Over a year ago

Thx. It realy better. In my example with st.setString(int, String); I don't like, that I will add columns to it and in that case every times I will have to recheck indexes.

Pat B · Accepted Answer · 2014-04-14 15:36:56Z

0

I believe the @ variables are used in stored procedures only...

Either you define a stored procedure or you can use the second option :

final String dbQuerry = "INSERT INTO test_table (TID, CREATED, OWNER, TITLE)\n" + 
                    "VALUES (?,?,?,?)\n" +
                    "ON DUPLICATE KEY UPDATE\n" + 
                    "CREATED=?, OWNER=?, TITLE=?";

  PreparedStatement st = mDBConnection.prepareStatement(dbQuerry);
st.setInt(1, Integer.valueOf(t.getParam("ID")));
st.setString(2, t.getParam("Date"));
st.setString(5, t.getParam("Date"));
st.setString(3, t.getParam("User"));
st.setString(6, t.getParam("User"));
st.setString(4, t.getParam("Title"));
st.setString(7, t.getParam("Title"));

answered Apr 14, 2014 at 15:36

Pat B

1,95526 silver badges41 bronze badges

2 Comments

spencer7593 Over a year ago

NOTE: The @var user defined variables can be used in regular SQL as well as stored programs. Using the special VALUES(foo) function as a reference to the value supplied for a column in the INSERT (i.e. the value that would have been inserted if the insert had succeeded) is the normal way we avoid having to pass the same values; and this is especially useful when it's more than one row being inserted.

Pat B Over a year ago

Thanks Spencer7593 for the info.

Collectives™ on Stack Overflow

SQL variables in queries in Java

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related