CODEWITHSUNDEEP
javamysqlsql
user1419742
user1419742

Reputation: 37

error due to single quotes in insert query

I am inserting values in a Mysql database from java file using -

String query = "INSERT INTO genes (sent, title) VALUES ('"+sent+"','"+title+"')";
Statement stmt = con.createStatement();
int rs = stmt.executeUpdate(query);

where sent and title are variable strings extracted after applying some algorithm. But this gives sql error when sent or title contains single qoutes.

Upvotes: 0

Views: 4797

Answers (6)

Vikram Jain
Vikram Jain

Reputation: 5588

Please, Remove ' from string or replace by \' from '.

Mysql allow only in \' format for special character.

Upvotes: 0

Pramod Kumar
Pramod Kumar

Reputation: 8014

String query = "INSERT INTO genes (sent, title) VALUES (?, ?)";
PreparedStatement pt = con.prepareStatement(query);
pt.setString(1, sent);
pt.setString(2, title);
pt.executeUpdate();

Upvotes: 1

Abdullah Jibaly
Abdullah Jibaly

Reputation: 54830

You should never concatenate SQL statements like this, instead, use prepared statements:

String query = "INSERT INTO genes (sent, title) VALUES (?,?)";
PreparedStatement stmt = con.prepareStatement(query);

p.setString(1, sent);
p.setString(2, title);
p.executeUpdate();

If you use the string concatenation method you are exposing yourself to dangerous sql-injection attacks.

Upvotes: 1

John Woo
John Woo

Reputation: 263933

You should use PreparedStatements for that. PreparedStatement is under java.sql.* namespace.

String insertString = "INSERT INTO genes (sent, title) VALUES (?,?)";
// con is your active connection
PreparedStatement insertX = con.prepareStatement(updateString); 
insertX.setString(1, sent);
insertX.setString(2, title);
insertX.executeUpdate();

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 24022

You should use PreparedStatement in fill the query parameters. It takes care of escaping the single quotes if any in the input parameters.

Modify your query and statement object as follows and it should be working: 

String query = "INSERT INTO genes (sent, title) VALUES (? , ?)";
PreparedStatement pst = con.prepareStatement( query );
pst.setString( 1, sent );
pst.setString( 2, title );

int insertResult = pst.executeUpdate();

Upvotes: 3

Andomar
Andomar

Reputation: 238296

Consider using a prepared statement with parameters:

PreparedStatement pstmt = con.prepareStatement(
    "INSERT INTO genes (sent, title) VALUES (?, ?)");
pstmt.setString(1, sent);
pstmt.setString(2, title);
pstmt.executeUpdate();

Upvotes: 4

Related Questions