Postgres，查询错误

Question

I do one query and I have syntax error. But for me I do all right.

Where I have error?

Thanks!

$str = "Moscow";
$data = $ci->crud_model->query(
    'select * from "Cities" where  "defaultName" ilike  %'.$str.'%'
);

Query is : select * from "Cities" where "defaultName" ilike %Moscow%

Answer 1

The LIKE/ILIKE operator takes two strings as its arguments. That is, the pattern has to be a quoted string, not just directly in the SQL query.

So instead of:

"defaultName" ilike %Moscow%

You need:

"defaultName" ilike '%Moscow%'

In PHP, you should be (at the very least) escaping the input to avoid SQL Injection. Probably CodeIgniter has facilities for escaping, or using parameterised queries, but at the very least you should do this:

$str = "Moscow";
$data = $ci->crud_model->query(
    'select * from "Cities" where  "defaultName" ilike  \'%'.pg_escape_string($str).'%\''
);

EDIT Per Craig Ringer's comment, the correct ways to escape or build safe queries with CodeIgniter are covered in this answer.

This is probably the simplest (note that the query parameter is automatically a string, and doesn't need extra quotes):

$str = "Moscow";
$data = $ci->crud_model->query(
    'select * from "Cities" where  "defaultName" ilike ?',
    array('%' . $str . '%')
);