1

I am trying to insert a bit of text into my MYSQL database via java.

the variable I am trying to put it into is a TEXT variable. My problem is that when I try to insert it, it picks up on the ' and thinks that it is a messed up insertion.

Here is the insert:

INSERT INTO TermsAndConditions (name,description,ownerID)  VALUES  ('bar condtions','Don't be stealin my stuff','2')

it thinks that by me having the word "Don't" is messed up

What I want to do is to do as little work as possible and tell the string just accept that all the characters need (their will probs be more than just the ' ) to have a "\'" or "\£" before them.

I know I can use replace but for ALL the characters would be a real pain! I am asking because their must be another simpler solution!

Thanks

Improve this question
1
  • Please add the Java code you use to execute this INSERT. Commented Aug 13, 2012 at 16:37

5 Answers 5

Reset to default
3

Your best bet will likely to be use prepared statements (which is a good idea on it's own if you're not controlling the input for the insert to avoid sql injection!)

This site provides an example of how to do it: http://www.exampledepot.com/egs/java.sql/InsertPs.html

For your example, it would be something like: 

// Prepare a statement to insert a record
String sql = "INSERT INTO TermsAndConditions (name,description,ownerID)  VALUES  (?,?,?)";
PreparedStatement pstmt = connection.prepareStatement(sql);

// Set the values
pstmt.setString(1, "bar condtions");
pstmt.setString(2, "Don't be stealin my stuff");
pstmt.setString(3, "2");

// Insert the row
pstmt.executeUpdate();
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I have used prepared statements before but didn't realise that they did this! I do not want to use them really as I know what I want to insert at this point so it really not needed and in fact less safe in the code I am writing. However, it works and should be fine. Thanks
2

You need to escape the single quote or, better still, use prepared statements.

Improve this answer

Comments

2

You should use a PreparedStatement to escape your special characters.

Improve this answer

Comments

1

Have you tried using prepared statements? The problem here is a matter of escaping Strings properly. Prepared statements can handle that for you and reduce unclean and ugly concatonation code.

http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Improve this answer

Comments

-1

You need to escape the single quote like this:

INSERT INTO TermsAndConditions (name,description,ownerID)  VALUES  ('bar condtions','Don\'t be stealin my stuff','2')
Improve this answer

3 Comments

That doesn't answer his question. He is looking to use prepared strings to avoid worrying about escape sequences.
@MrHappyAsthma The OP said absolutely nothing about prepared statements. I was simply pointing out why his insert was not working. Java and its various DB connection libraries provide any number of ways to achieve this.
I agree. Originally I added such a solution as a comment. But after re-reading he clearly states that he doesn't want to have to worry about adding "\" escape sequences. Regardless, its a shame you got a down vote :( That was not my doing. I was just pointing out that this doesn't help his goal of the question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.