CODEWITHSUNDEEP
mysqlsyntax
user523956
user523956

Reputation: 511

problem in where clause of mysql query

Hi I am generating messahedigest with SHA1 of a file(having extension .eml, as it contains email info)and then storing it to the table named web_de in column messagedigest. Why can't I execute following query in mysql ?? and also not in java...

SELECT slno FROM `webcrawler`.`web_de` 
where messagedigest='?Ê'?`®o1F±[øT¤?¿!€' ;

while I could execute query like

SELECT slno FROM `webcrawler`.`web_de` 
where messagedigest= ')@Ä€ó…ªã³§°óÚdv~θ`';

Pl note that I am trying to execute that query in mysql workbench 5.2.32 and using mysql 5.1

Can anybody help me out here please ???

Thanks in advance

Upvotes: 1

Views: 317

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220762

You have to escape that single quote in the first query:

where messagedigest = '?Ê''?`®o1F±[øT¤?¿!€' ;

Escaping is done by duplicating quotes:

''

(btw: as you see, even the stackoverflow syntax highlighter wasn't able to properly format your string...)

On the other hand, you shouldn't inline values in SQL for various reasons (security, performance). Since you're using Java, use a PreparedStatement instead:

// Prepare a statement with a bind variable : ?
PreparedStatement ps = connection.prepareStatement(
    "SELECT slno FROM webcrawler.web_de WHERE messagedigest = ?");

// Bind your string to the first bind variable
ps.setString(1, "?Ê'?`®o1F±[øT¤?¿!€");

// ...
ResultSet rs = ps.executeQuery();

Upvotes: 3

Paul W
Paul W

Reputation: 1049

the messagedigest value has a quote in it. If you escape the quote it should work, but... you might be better off encoding the message digest before trying to write it to the database.

Upvotes: 0

anothershrubery
anothershrubery

Reputation: 20993

The ' is not being escaped. Replace it with double quotes '' so it reads as:

SELECT slno FROM `webcrawler`.`web_de`  
where messagedigest='?Ê''?`®o1F±[øT¤?¿!€';

EDIT: Too slow! :P

You can also escape it by using \' also

Upvotes: 0

Related Questions