mysql查询的where子句中的问题

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...

嗨，我正在使用文件的SHA1生成messahedigest（扩展名为.eml，因为它包含电子邮件信息），然后将其存储到messagedigest列中名为web_de的表中。为什么我不能在mysql中执行以下查询？而且不是在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

Pl注意我试图在mysql workbench 5.2.32中执行该查询并使用mysql 5.1

Can anybody help me out here please ???

有人可以帮帮我吗???

Thanks in advance

提前致谢

3 个解决方案

#1

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 * 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:

另一方面，您不应出于各种原因（安全性，性能）在SQL中内联值。由于您使用的是Java，请使用PreparedStatement：

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

#2

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

编辑：太慢了！：P

You can also escape it by using \' also

您也可以使用\'来逃避它

#3

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.

messagedigest值中包含引号。如果你逃避引用它应该工作，但是...你可能最好在编写消息摘要之前尝试将其写入数据库。

#1