Re: Feature request - psql --quote-variable

On Tue, Mar 7, 2017 at 1:29 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> 2017-03-07 21:04 GMT+01:00 Caleb Cushing <xenoterracide(at)gmail(dot)com>:
>
>> Thank you. Apparently I never saw this response, for some reason...
>>
>> So reading that leaves me confused on one point, which is the right way
>> to do it if you're inserting an integer? would this be right? is there a
>> difference between the single and double quotes here?
>>
>
> postgres=# create table foo(a int);
> CREATE TABLE
> Time: 276,386 ms
> postgres=# insert into foo values('1');
> INSERT 0 1
> Time: 72,357 ms
>
>
>
>>
>> (presume id is a bigint)
>> `insert into foo ( id ) values ( :'var' )`
>>
>
> double quotes are used for identifiers. 'xxxx' is string literal, "xxxx"
> is sql identifier like table name or column name.
>
>
​This is a bit of cheating since the system, knowing that "a" is of type
"int", is allowed to implicitly cast an unadorned/untyped literal '1'​.

What is really happening is:

insert into foo (a) values ('1'::integer);

IOW - it is OK - and cheap - to place integers into single quotes and then
cast them in order to add anti-injection features to the query.

Dave