CODEWITHSUNDEEP
javajdbcmariadbprepared-statement
pumper
pumper

Reputation: 137

How to handle single quotes in PreparedStatement?

I want to use a prepared statement like this:

acl = conn.prepareStatement("INSERT INTO acls_t(user,topic,rw) values((SELECT id from users_t where username=?),'?/#',1);");
mqtt_acl.setString(1, name);
mqtt_acl.setString(2, uuid);

My database is MariaDB and does not accept this. It returns the following error:

java.sql.SQLException: Could not set parameter at position 2 (values was '3aa4ea54-3105-47a3-9bfb-c17c4348d84a')
Query - conn:56(M)  - "INSERT INTO acls_t(user,topic,rw) values((SELECT id from users_t where username=?),'?/#',1);"

How can I handle this?

Upvotes: 2

Views: 1582

Answers (2)

forpas
forpas

Reputation: 164194

Your sql statement could be simplified to this:

INSERT INTO acls_t(user,topic,rw) SELECT id, CONCAT(?, '/#'), 1 FROM users_t WHERE username = ?;

without the use of VALUES for which you must nest the SELECT statement.
So change your code to:

acl = conn.prepareStatement("INSERT INTO acls_t(user,topic,rw) SELECT id, CONCAT(?, '/#'), 1 FROM users_t WHERE username = ?;");
mqtt_acl.setString(1, uuid);
mqtt_acl.setString(2, name);

With this code if there is no user in the table users_t with username equal to the value of the variable name then no row will be inserted, but your code will try to insert a row to the table acls_t and set the column user with null.

Upvotes: 1

Arvind Kumar Avinash
Arvind Kumar Avinash

Reputation: 79580

Do it as follows:

acl = conn.prepareStatement("INSERT INTO acls_t(user,topic,rw) values((SELECT id from users_t where username=?),?,1);");
mqtt_acl.setString(1, name);
mqtt_acl.setString(2, uuid + "/#");

Upvotes: 2

Related Questions