problem in where clause of mysql query

Question

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

Lukas Eder · Accepted Answer · 2011-05-12 14:04:36Z

3

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();

edited May 12, 2011 at 14:04

answered May 12, 2011 at 13:59

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anothershrubery · Accepted Answer · 2011-05-12 14:00:40Z

0

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

answered May 12, 2011 at 14:00

anothershrubery

21.1k16 gold badges56 silver badges102 bronze badges

Comments

Paul W · Accepted Answer · 2011-05-12 14:00:53Z

0

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.

answered May 12, 2011 at 14:00

Paul W

1,0499 silver badges18 bronze badges

2 Comments

user523956 Over a year ago

Could u pl give me some idea as to how can I do encoding of the message digest ?

Paul W Over a year ago

@user523956 you could use UUEencode/UUDecode. There are various implementations out there if you google for them. There is a sun implementation that used to be part of java, but it isn't part of the official API spec, so it could go away.

Collectives™ on Stack Overflow

problem in where clause of mysql query

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related