How to use prepared statements in DO statements?
I have a fairly convoluted query where I am trying to use prepared statements to protect my database from SQL injection. I have essentially chained multiple queries under a single begin-end block.
This composite query first checks if a user sessions exists, then if the user has been banned, then if the tags entered by the user are valid and finally inserts the post into the database.
Here is the query:
query = "DO
$$
BEGIN
IF
(select exists(select user_id from sessions where unqid = $1 and user_id = $2))
THEN
IF
(select banned_till from users where unqid = $2) > now()
THEN
RAISE EXCEPTION 'User has been banned!';
ELSE
IF (
Select ( SELECT array_agg(DISTINCT name) FROM allowed_tags) @> $3)
THEN
insert into posts (unqid, title, link, content, user_id, user_nick, user_flair,
tags, tags_details, likes, likes_details)
SELECT $4, $5, $6, $7,
$2, user_nick, user_flair,
$8, $9, 1, $10
from users where unqid = $2;
ELSE
RAISE EXCEPTION 'Fake tags detected!';
END IF;
END IF;
ELSE
RAISE EXCEPTION 'User is not logged in';
END IF;
END
$$;"
DB.exec query,
session_id, session_user, tags_list, unqid, title, link, content,
tags_obj.to_json, tags_details_obj.to_json, likes_obj.to_json
This query works fine when I am using string interpolation. But when I tried to use a prepared statement, I started getting;
bind message supplies 10 parameters, but prepared statement "" requires 0
How do I use prepared statements in my query?
postgresql transactions prepared-statement
edited Nov 23 '18 at 12:34
Laurenz Albe
51.8k103052
asked Nov 22 '18 at 20:13
Rishav SharanRishav Sharan
85152237
add a comment |
I have a fairly convoluted query where I am trying to use prepared statements to protect my database from SQL injection. I have essentially chained multiple queries under a single begin-end block.
This composite query first checks if a user sessions exists, then if the user has been banned, then if the tags entered by the user are valid and finally inserts the post into the database.
Here is the query:
query = "DO
$$
BEGIN
IF
(select exists(select user_id from sessions where unqid = $1 and user_id = $2))
THEN
IF
(select banned_till from users where unqid = $2) > now()
THEN
RAISE EXCEPTION 'User has been banned!';
ELSE
IF (
Select ( SELECT array_agg(DISTINCT name) FROM allowed_tags) @> $3)
THEN
insert into posts (unqid, title, link, content, user_id, user_nick, user_flair,
tags, tags_details, likes, likes_details)
SELECT $4, $5, $6, $7,
$2, user_nick, user_flair,
$8, $9, 1, $10
from users where unqid = $2;
ELSE
RAISE EXCEPTION 'Fake tags detected!';
END IF;
END IF;
ELSE
RAISE EXCEPTION 'User is not logged in';
END IF;
END
$$;"
DB.exec query,
session_id, session_user, tags_list, unqid, title, link, content,
tags_obj.to_json, tags_details_obj.to_json, likes_obj.to_json
This query works fine when I am using string interpolation. But when I tried to use a prepared statement, I started getting;
bind message supplies 10 parameters, but prepared statement "" requires 0
How do I use prepared statements in my query?
postgresql transactions prepared-statement
edited Nov 23 '18 at 12:34
Laurenz Albe
51.8k103052
asked Nov 22 '18 at 20:13
Rishav SharanRishav Sharan
85152237
add a comment |
I have a fairly convoluted query where I am trying to use prepared statements to protect my database from SQL injection. I have essentially chained multiple queries under a single begin-end block.
This composite query first checks if a user sessions exists, then if the user has been banned, then if the tags entered by the user are valid and finally inserts the post into the database.
Here is the query:
query = "DO
$$
BEGIN
IF
(select exists(select user_id from sessions where unqid = $1 and user_id = $2))
THEN
IF
(select banned_till from users where unqid = $2) > now()
THEN
RAISE EXCEPTION 'User has been banned!';
ELSE
IF (
Select ( SELECT array_agg(DISTINCT name) FROM allowed_tags) @> $3)
THEN
insert into posts (unqid, title, link, content, user_id, user_nick, user_flair,
tags, tags_details, likes, likes_details)
SELECT $4, $5, $6, $7,
$2, user_nick, user_flair,
$8, $9, 1, $10
from users where unqid = $2;
ELSE
RAISE EXCEPTION 'Fake tags detected!';
END IF;
END IF;
ELSE
RAISE EXCEPTION 'User is not logged in';
END IF;
END
$$;"
DB.exec query,
session_id, session_user, tags_list, unqid, title, link, content,
tags_obj.to_json, tags_details_obj.to_json, likes_obj.to_json
This query works fine when I am using string interpolation. But when I tried to use a prepared statement, I started getting;
bind message supplies 10 parameters, but prepared statement "" requires 0
How do I use prepared statements in my query?
postgresql transactions prepared-statement
edited Nov 23 '18 at 12:34
Laurenz Albe
51.8k103052
asked Nov 22 '18 at 20:13
Rishav SharanRishav Sharan
85152237
I have a fairly convoluted query where I am trying to use prepared statements to protect my database from SQL injection. I have essentially chained multiple queries under a single begin-end block.
This composite query first checks if a user sessions exists, then if the user has been banned, then if the tags entered by the user are valid and finally inserts the post into the database.
Here is the query:
query = "DO
$$
BEGIN
IF
(select exists(select user_id from sessions where unqid = $1 and user_id = $2))
THEN
IF
(select banned_till from users where unqid = $2) > now()
THEN
RAISE EXCEPTION 'User has been banned!';
ELSE
IF (
Select ( SELECT array_agg(DISTINCT name) FROM allowed_tags) @> $3)
THEN
insert into posts (unqid, title, link, content, user_id, user_nick, user_flair,
tags, tags_details, likes, likes_details)
SELECT $4, $5, $6, $7,
$2, user_nick, user_flair,
$8, $9, 1, $10
from users where unqid = $2;
ELSE
RAISE EXCEPTION 'Fake tags detected!';
END IF;
END IF;
ELSE
RAISE EXCEPTION 'User is not logged in';
END IF;
END
$$;"
DB.exec query,
session_id, session_user, tags_list, unqid, title, link, content,
tags_obj.to_json, tags_details_obj.to_json, likes_obj.to_json
This query works fine when I am using string interpolation. But when I tried to use a prepared statement, I started getting;
bind message supplies 10 parameters, but prepared statement "" requires 0
How do I use prepared statements in my query?
postgresql transactions prepared-statement
postgresql transactions prepared-statement
edited Nov 23 '18 at 12:34
Laurenz Albe
51.8k103052
asked Nov 22 '18 at 20:13
Rishav SharanRishav Sharan
85152237
edited Nov 23 '18 at 12:34
Laurenz Albe
51.8k103052
asked Nov 22 '18 at 20:13
Rishav SharanRishav Sharan
85152237
edited Nov 23 '18 at 12:34
Laurenz Albe
51.8k103052
edited Nov 23 '18 at 12:34
Laurenz Albe
51.8k103052
edited Nov 23 '18 at 12:34
Laurenz Albe
51.8k103052
51.8k103052
asked Nov 22 '18 at 20:13
Rishav SharanRishav Sharan
85152237
asked Nov 22 '18 at 20:13
Rishav SharanRishav Sharan
85152237
asked Nov 22 '18 at 20:13
Rishav SharanRishav Sharan
85152237
85152237
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You cannot use a DO statement as a prepared statement.
I recommend that you use two statements:
one get the three results that you need to determine if there is an error condition
one to run the
INSERTstatement
The second of these would be a regular prepared statement.
It seems to me that you are mixing up transactions and BEGIN ... END blocks in PL/pgSQL.
answered Nov 23 '18 at 11:51
Laurenz AlbeLaurenz Albe
51.8k103052
Thank you. I was worried about that. Guess I have to go back and refactor my queries.
– Rishav Sharan
Nov 23 '18 at 14:39
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53437532%2fhow-to-use-prepared-statements-in-do-statements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You cannot use a DO statement as a prepared statement.
I recommend that you use two statements:
one get the three results that you need to determine if there is an error condition
one to run the
INSERTstatement
The second of these would be a regular prepared statement.
It seems to me that you are mixing up transactions and BEGIN ... END blocks in PL/pgSQL.
answered Nov 23 '18 at 11:51
Laurenz AlbeLaurenz Albe
51.8k103052
Thank you. I was worried about that. Guess I have to go back and refactor my queries.
– Rishav Sharan
Nov 23 '18 at 14:39
add a comment |
You cannot use a DO statement as a prepared statement.
I recommend that you use two statements:
one get the three results that you need to determine if there is an error condition
one to run the
INSERTstatement
The second of these would be a regular prepared statement.
It seems to me that you are mixing up transactions and BEGIN ... END blocks in PL/pgSQL.
answered Nov 23 '18 at 11:51
Laurenz AlbeLaurenz Albe
51.8k103052
Thank you. I was worried about that. Guess I have to go back and refactor my queries.
– Rishav Sharan
Nov 23 '18 at 14:39
add a comment |
You cannot use a DO statement as a prepared statement.
I recommend that you use two statements:
one get the three results that you need to determine if there is an error condition
one to run the
INSERTstatement
The second of these would be a regular prepared statement.
It seems to me that you are mixing up transactions and BEGIN ... END blocks in PL/pgSQL.
answered Nov 23 '18 at 11:51
Laurenz AlbeLaurenz Albe
51.8k103052
You cannot use a DO statement as a prepared statement.
I recommend that you use two statements:
one get the three results that you need to determine if there is an error condition
one to run the
INSERTstatement
The second of these would be a regular prepared statement.
It seems to me that you are mixing up transactions and BEGIN ... END blocks in PL/pgSQL.
answered Nov 23 '18 at 11:51
Laurenz AlbeLaurenz Albe
51.8k103052
edited Nov 23 '18 at 12:35
answered Nov 23 '18 at 11:51
Laurenz AlbeLaurenz Albe
51.8k103052
answered Nov 23 '18 at 11:51
Laurenz AlbeLaurenz Albe
51.8k103052
answered Nov 23 '18 at 11:51
Laurenz AlbeLaurenz Albe
51.8k103052
51.8k103052
Thank you. I was worried about that. Guess I have to go back and refactor my queries.
– Rishav Sharan
Nov 23 '18 at 14:39
add a comment |
Thank you. I was worried about that. Guess I have to go back and refactor my queries.
– Rishav Sharan
Nov 23 '18 at 14:39
Thank you. I was worried about that. Guess I have to go back and refactor my queries.
– Rishav Sharan
Nov 23 '18 at 14:39
Thank you. I was worried about that. Guess I have to go back and refactor my queries.
– Rishav Sharan
Nov 23 '18 at 14:39
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53437532%2fhow-to-use-prepared-statements-in-do-statements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
