Apostrophe when reading from d/b

[newbie14]

Dear All,
I am inserting a string value into my mysql db which had apostrophe with no problem because I do this when inserting.

String cleanMessage = oriMessage.replace("'","\\\'");

The problem when I try to read from the same table I tried like this

level2[0] = level2[0].replace("'","\\\'");
String queryCheck = "Select dataID from tblData where dataString='"+level2[0]+"'";

.
What I notice in between the data where I try to read have like this \' ? So how to solve this problem?

[thines01]

1) Take out line 1.
2) Use replace "'" with "''"

That all depends on the data.
Do you have a sample string you're trying to query?

[newbie14]

Dear Thines,
Here is a sample data

00010080,88490000'29111100383300459465N10110286E001000*2084#

. Which first line do you mean this level2[0] = level2[0].replace("'","\\\'");?

[newbie14]

Dear Thines,
I tried your method what is creates is the double single quote''. So when I run the query is gives me zero results as the the db has only a single quote '

Select dataID from tblData where dataString='00010080,88490000''29111100383300459465N10110286E001000*2084#'

[thines01]

I've tried this on three different types of databases and they all use the double-single quotes.

MySQL, SQL Server and MS Access

[newbie14]

Dear Thines,
You are right the problem I was missing one last chracter in my string. Sorry very much.

[~s.o.s~]

Use a PreparedStatement which automatically takes care of escaping special characters and at the same time protecting you from SQL Injection attacks.

PreparedStatement pstmt = connection.prepareStatement("select dataid from tbldata where datastring=?");
pstmt.setString(1, level2[0]);
ResultSet rs = pstmt.executeQuery();
// use rs

Use the same trick for inserting data so that you don't have to worry about replacing stuff. This makes it easier for the programmer to write queries in Java (i.e. no concatenation and escaping) and has the possibility of improving performance if the database and the JDBC driver supports statement pooling.

[mKorbel]

my question about BackSlashes

[newbie14]

Dear All,
I would like to learn more about prepared statement. So moving from my current queries to this type of statement what exactly must I do? Must I do something at the db level?

[thines01]

Here is a tutorial:
http://www.jdbc-tutorial.com/jdbc-prepared-statements.htm