pg_query_params插入默认值或使用函数时出错

Question

pg_query_params seems not working as expected:

this works:

INSERT INTO users (username,password,secretcode) VALUES 'test',crypt(:password,gen_salt(md5)), 'DEFAULT'

secretcode field have a default generated value

This doesn't works:

pg_query_params($Db, 'INSERT INTO users (username,password,secretcode) VALUES ($1, $2, $3)', array ($USR,crypt($PWD,gen_salt(md5)),'DEFAULT');

password field contains the string 'crypt($PWD,gen_salt(md5))'

secretcode field contains 'DEFAULT' string

Is this the correct behavior or a bug ?

Thanks

Dan Perez

Answer 1

Parameters are designed to send exactly the literal text you wrote. The whole point is that they don't execute functions or otherwise get interpreted as SQL syntax.

DEFAULT is a keyword. That's not what you're supplying, you're sending the text 'DEFAULT'.

Similarly, you're sending the text crypt($PWD,gen_salt(md5)) to PostgreSQL.

If you want to insert using a query you can do that, but you must modify the SQL. E.g.

pg_query_params(
  $Db,
  'INSERT INTO users (username,password,secretcode) VALUES ($1, crypt($2, gen_salt('md5')), DEFAULT)', array ($USR,$PWD)
);

As you can see, you can put arbitrary expressions in the VALUES clause, as well as keywords like DEFAULT or NEW. You can't pass them as query parameters.

I'd really like it if there were "special" client values that you could pass, magic constants that were interpreted as keywords like DEFAULT. It doesn't work that way, though; the DEFAULT keyword is interpreted at parse/plan time, not parameter binding time.

Also, you must quote the hash type in gen_salt - 'md5' not just md5.

Finally - crypt is not an acceptable way to hash a password.