sql查询中这个〜*语法的功能是什么？

Question

I am trying to understand a script to implement new functions and options. In order to search in sql tables they used the following syntax

$sqlquery = "SELECT DISTINCT \"Name\", \"FileName\", \"Status\", lower(\"Name\") FROM \"" . $SearchedTable . "\" WHERE ";

$sqlquery=$sqlquery." (\"Name\" ~* '".$text[$i]."' OR \"DNA\" ~* '".$text[$i]."')";

• What I would like to know is why they use \"Name\" (I read somewhere that it has something to do with sql injections).
• I also couldn't find the reason why to use this syntax ~* instead of =. Or does it means something else?

Answer 1

General note: Your current query uses string concatenation, and therefore is prone to SQL injection. The best fix on your PHP side would be to use a prepared statement.

That being said, your SQL syntax appears to be Postgres, not MySQL. Here is the rough query:

SELECT DISTINCT Name, FileName, Status, lower(Name)
FROM yourTable
WHERE Name ~* ? OR DNA ~* ?;

The ~* operator in Postgres does case insensitive regex matching. So, for example, to match any Name which starts with mi, you could use:

WHERE Name ~* '^mi'

Therefore, what you should be binding to the two placeholders are regular expressions.