From: "Lembark, Steven" <Steven.Lembark@broadridge.com>
To: "psycopg@postgresql.org" <psycopg@postgresql.org>
CC: "Loga, Dharani" <Dharani.Loga@broadridge.com>
Subject: Using standard SQL placeholders in PG
Thread-Topic: Using standard SQL placeholders in PG
Thread-Index: AdfAM3VxI+kFbOwTT6+PKFw9q9wVOA==
Date: Wed, 13 Oct 2021 13:11:14 +0000
Message-ID: 
 <BN0PR20MB391249E4640F43A8A14B84A4FDB79@BN0PR20MB3912.namprd20.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?m8b+xM9L/l+UVeIIFzNAdm9FvsHOPlQ1E0LAL8XWdiOdFGqsyzleG29nc+3W?=
 =?us-ascii?Q?cREF7UFHcpHI3pSox9cbzTKFMAR2uV4Xp9i9+TEYdwa0KtuikHqW8P+MzD25?=
 =?us-ascii?Q?GSOUW+0nUEW7Y+CJ8Wn+lfqSMQ4m4TL1RNF1qtdNRNL5pw1+x+bg07gtBNPH?=
 =?us-ascii?Q?4YL35no4r2li0zvNX5oSgjgcmT9CvpBsh9Awv0ToYZtSygj+QLZE8mug8Jfn?=
 =?us-ascii?Q?2Rcv9Qf1QjYiK/mE6JXxd33FusFzp4nEuC0/13B2YNh0fLv7BY+tzKw3Tg6E?=
 =?us-ascii?Q?5MqeM4Uv9xllJ+OcSxHt8/Ga3WU0uJboqNhREv8ZqiXC3nqwVEmkBzXPi2dO?=
 =?us-ascii?Q?LqMYJ1uv/4sJ5M1YeZGa6oVT24V21nMBiIDwNF08CeqziZ0Vs8T/u1NNO+Ss?=
 =?us-ascii?Q?Rcb1daAe9Bw2uRY/PSL0scSjM8d2prQ3Ka20a9cTpqDdReU9usq98ApeLYhW?=
 =?us-ascii?Q?eWfuvAmDi/B0XIyb4NNE//3oHwyIFT7SQXzGIpU2xjBxvOfSXwSlMoh+Q/3M?=
 =?us-ascii?Q?xQRQJ+jPElWXYJMPhaqxa00fU0hHy8Owx76GZJ4tzV5ono1Eu1T+Lld8FEwg?=
 =?us-ascii?Q?FPg5I6Yugm2w+Rd24svVVlMDxkEGfBFmlU7N2fvo1cOtghvgynycLtktmIoD?=
 =?us-ascii?Q?vA4obcY0wQmWW4UDaKqkBcjeItxvvwsdGEOM/KxXZtgvQwVif3yZIqx6KeKL?=
 =?us-ascii?Q?DcEWTnZiyrCHLh2F9H8gIZFPhiGIw018AWOLg9ZsViw21Uam6gMOSK7a/HoJ?=
 =?us-ascii?Q?u0HKlXBYebwRb3c0dimmZ0X3Km+rp0r1Dd0tdRF7yAiVOXdAqcPREgjrnwA7?=
 =?us-ascii?Q?BuqVPWZ8ccrp0khGDBkRfcDUIquYyuRgLZOW4/vQgOd1s3VPz7dtDebm+vWj?=
 =?us-ascii?Q?503SqUtSRarz+JDj4spy2EkIKgnCSAAZ/yhOPr04f0UUNkNda9yQ7LcWbo0O?=
 =?us-ascii?Q?b+D6UNP3d5ONXc8U7JMI+rdI+fgYDD8rj1rN0H4z4+CtnhsYJdZdAfTXOtpT?=
 =?us-ascii?Q?6LPLEa/O/tP8Au5DA+pa4+UxAvrHume93tvcpf4ShOLySUzzhOS8IUvNcXyK?=
 =?us-ascii?Q?3K9sC84EdTa2At+RZiZ4UqaY1ue0+OhK63CEzW2gB4YjylzZC1CAa8XB/UhT?=
 =?us-ascii?Q?rpYqgKFKUAvxo20x29xt52sChrsJQQQWswCg6KFDh6wsJL+G8n3Lbzins9nN?=
 =?us-ascii?Q?+oBSlVxRVp2nAunZz+uTdAfcwFIHO3TpJQu/GNoEi1+Q6CUSJt65p7ZZoo9F?=
 =?us-ascii?Q?T5Jn8UFxxS11/1g6QtMLalGU2758noPvZqoP3s/3KzK47Q1TaKQOeY+r6wcF?=
 =?us-ascii?Q?wiWlV6TUDSGfgI/cnxqchTXF?=
Content-Type: multipart/alternative;
	boundary="_000_BN0PR20MB391249E4640F43A8A14B84A4FDB79BN0PR20MB3912namp_"
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BN0PR20MB3912.namprd20.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 309bfbcc-fe23-44c2-2857-08d98e4aef90
X-MS-Exchange-CrossTenant-originalarrivaltime: 13 Oct 2021 13:11:14.6158
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: a3198c8c-0642-4649-849d-daacc3298f83
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 fWztC7cQu9HwTcjjP7K0UrUvg+LajP7B7XM1B1wZvyiE6yt3qL1MHidOIms7o1C5YdezLkLmOJQWBEFcDNW1IgbelmRXFpn4v3n8Ukwf2s8=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN8PR20MB2451
X-OriginatorOrg: broadridge.com
X-SYMANTEC-INSPECTED: YES
X-Proofpoint-GUID: CQoq-xS0RvExKXoddTZV3h0nV63BY6f6
X-Proofpoint-ORIG-GUID: CQoq-xS0RvExKXoddTZV3h0nV63BY6f6
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.182.1,Aquarius:18.0.790,Hydra:6.0.425,FMLib:17.0.607.475
 definitions=2021-10-13_05,2021-10-13_02,2020-04-07_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0
 spamscore=0 suspectscore=0
 phishscore=0 mlxscore=0 priorityscore=1501 adultscore=0 mlxlogscore=586
 impostorscore=0 clxscore=1011 bulkscore=0 lowpriorityscore=0
 malwarescore=0 classifier=spam adjust=0 reason=mlx scancount=1
 engine=8.12.0-2109230001 definitions=main-2110130088
List-Id: <psycopg.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:psycopg@lists.postgresql.org>
List-Owner: <mailto:psycopg-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/psycopg>
Archived-At: 
 <https://www.postgresql.org/message-id/BN0PR20MB391249E4640F43A8A14B84A4FDB79%40BN0PR20MB3912.namprd20.prod.outlook.com>
Precedence: bulk

--_000_BN0PR20MB391249E4640F43A8A14B84A4FDB79BN0PR20MB3912namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

PostgreSQL ("PG") supports the notion of placeholders, as do many other rel=
ational databases. The placeholder notation in PG uses $X within the SQL. F=
or example:

select     foo
from       bar
where      bletch =3D $1

is valid SQL. The doller-notation allows re-use of positional parameters an=
d avoids SQL injection entirely by placing the input is appropriately-typed=
 buffers w/in the SQL statement handler. It also avoids issues with double-=
interpolating quotes as the placeholders are not literals and do not requir=
e SQL-quoting to be interpolated properly.

We have quite a bit of SQL here that has to be shared between Python and ot=
her packages, so using standard PG SQL statements is required.

Q: Is there any way to prepare and execute standard PG SQL statements with =
dollar placeholders in PsychoPG2?

Thanks

This message and any attachments are intended only for the use of the addre=
ssee and may contain information that is privileged and confidential. If th=
e reader of the message is not the intended recipient or an authorized repr=
esentative of the intended recipient, you are hereby notified that any diss=
emination of this communication is strictly prohibited. If you have receive=
d this communication in error, please notify us immediately by e-mail and d=
elete the message and any attachments from your system.

--_000_BN0PR20MB391249E4640F43A8A14B84A4FDB79BN0PR20MB3912namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:m=3D"http://sc=
hemas.microsoft.com/office/2004/12/omml" xmlns=3D"http://www.w3.org/TR/REC-=
html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii">
<meta name=3D"Microsoft Theme 2.00" content=3D"Journal 000">
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Verdana;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
	{font-family:"Lucida Console";
	panose-1:2 11 6 9 4 5 4 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:12.0pt;
	font-family:"Verdana",sans-serif;
	color:#6A6A6A;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Lucida Console";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor=3D"white" lang=3D"EN-US" link=3D"#D03505" vlink=3D"#FF7045" s=
tyle=3D"word-wrap:break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext">PostgreSQL (&quot;PG&quot;) supports t=
he notion of placeholders, as do many other relational databases. The place=
holder notation in PG uses $X within the SQL. For example:<br>
<br>
select&nbsp;&nbsp;&nbsp;&nbsp; foo<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext">from&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; bar<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext">where&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bl=
etch =3D $1<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext">is valid SQL. The doller-notation allo=
ws re-use of positional parameters and avoids SQL injection entirely by pla=
cing the input is appropriately-typed buffers
 w/in the SQL statement handler. It also avoids issues with double-interpol=
ating quotes as the placeholders are not literals and do not require SQL-qu=
oting to be interpolated properly.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext">We have quite a bit of SQL here that h=
as to be shared between Python and other packages, so using standard PG SQL=
 statements is required.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext">Q: Is there any way to prepare and exe=
cute standard PG SQL statements with dollar placeholders in PsychoPG2?<o:p>=
</o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext">Thanks<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt;font-family:&quot;Lu=
cida Console&quot;;color:windowtext"><o:p>&nbsp;</o:p></span></p>
</div>

<DIV>
This message and any attachments are intended only for the use of the addre=
ssee and may contain information that is privileged and confidential. If th=
e reader of the message is not the intended recipient or an authorized repr=
esentative of the intended recipient, you are hereby notified that any diss=
emination of this communication is strictly prohibited. If you have receive=
d this communication in error, please notify us immediately by e-mail and d=
elete the message and any attachments from your system.<BR>
</DIV></body>
</html>

--_000_BN0PR20MB391249E4640F43A8A14B84A4FDB79BN0PR20MB3912namp_--