如何使用WHERE LIKE SQL子句？

sprintf(query, "SELECT username, msg, ts, lat, lon FROM tweet WHERE msg LIKE '%'%s'%' ORDER BY ts DESC", keyword);

The way I did it, is giving me errors. How am I supposed to format the '%keyword%' into SQL using printf?

我这样做的方式是给我错误。我应该如何使用printf将'%keyword%'格式化为SQL?

2 个解决方案

#1

If my understanding SQL Query statements is correct, when the value of keyword is NBA, you want the query string to look like:

如果我理解SQL Query语句是正确的,当关键字的值是NBA时,您希望查询字符串看起来像:

"SELECT username, msg, ts, lat, lon FROM tweet WHERE msg LIKE '%NBA%' ORDER BY ts DESC"

In that case, the format of the sprintf statement needs to be:

在这种情况下,sprintf语句的格式必须是:

"SELECT username, msg, ts, lat, lon FROM tweet WHERE msg LIKE '%%%s%%' ORDER BY ts DESC"

#2

You need to quote the percent sign by doubling it %%

您需要通过将百分号加倍来引用百分号%%

sprintf(query, "SELECT username, msg, ts, lat, lon FROM tweet WHERE msg LIKE '%%%s%%' ORDER BY ts DESC", keyword);
                                                                              ^^  ^^

As noted: concatenating strings is error prone and may be subject to SQL injection attacks. Use parameterised queries instead.

如上所述:连接字符串容易出错,可能会受到SQL注入攻击。请改用参数化查询。

#1