How to escape quotes in django sql formatting
up vote
0
down vote
favorite
I have the following code:
# looks like: "('tt1098327','tt3819668','tt0049251')", <type 'str'>
ids_as_string = "(-1)" if not ids else ("('" + "','".join(ids).strip("',") + "')")
items = list(Item.objects.raw("""SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s)""", (q, ids_as_string )))
The problem with this is that the sql formatter tries to escape the quotes that I have added in for the tuple:
DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''('tt1098327','tt3819668','tt0049251','tt3878722
What would be the correct way to do the above?
python sql django
asked Nov 11 at 22:56
David L
35310
add a comment |
up vote
0
down vote
favorite
I have the following code:
# looks like: "('tt1098327','tt3819668','tt0049251')", <type 'str'>
ids_as_string = "(-1)" if not ids else ("('" + "','".join(ids).strip("',") + "')")
items = list(Item.objects.raw("""SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s)""", (q, ids_as_string )))
The problem with this is that the sql formatter tries to escape the quotes that I have added in for the tuple:
DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''('tt1098327','tt3819668','tt0049251','tt3878722
What would be the correct way to do the above?
python sql django
asked Nov 11 at 22:56
David L
35310
can you show whatidsis?
– Jack Herer
Nov 12 at 1:26
what exactly are you trying to do? do you want to create a tuple of ids to use as a variable in sql
– Jack Herer
Nov 12 at 1:28
why are you joining with a comma and then stripping at the comma again?
– Jack Herer
Nov 12 at 1:30
Why are you doing this as raw SQL rather than an ORM query, which would take care of all relevant escaping?
– Daniel Roseman
Nov 12 at 7:37
@DanielRoseman if you can show me how to do it as a regular ORM query I'm all for it -- note there's also a union in the query (not mentioned above) and two limits applied, so not really a straightforward query.
– David L
Nov 12 at 23:48
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have the following code:
# looks like: "('tt1098327','tt3819668','tt0049251')", <type 'str'>
ids_as_string = "(-1)" if not ids else ("('" + "','".join(ids).strip("',") + "')")
items = list(Item.objects.raw("""SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s)""", (q, ids_as_string )))
The problem with this is that the sql formatter tries to escape the quotes that I have added in for the tuple:
DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''('tt1098327','tt3819668','tt0049251','tt3878722
What would be the correct way to do the above?
python sql django
asked Nov 11 at 22:56
David L
35310
I have the following code:
# looks like: "('tt1098327','tt3819668','tt0049251')", <type 'str'>
ids_as_string = "(-1)" if not ids else ("('" + "','".join(ids).strip("',") + "')")
items = list(Item.objects.raw("""SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s)""", (q, ids_as_string )))
The problem with this is that the sql formatter tries to escape the quotes that I have added in for the tuple:
DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''('tt1098327','tt3819668','tt0049251','tt3878722
What would be the correct way to do the above?
python sql django
python sql django
asked Nov 11 at 22:56
David L
35310
asked Nov 11 at 22:56
David L
35310
asked Nov 11 at 22:56
David L
35310
asked Nov 11 at 22:56
David L
35310
asked Nov 11 at 22:56
David L
35310
35310
can you show whatidsis?
– Jack Herer
Nov 12 at 1:26
what exactly are you trying to do? do you want to create a tuple of ids to use as a variable in sql
– Jack Herer
Nov 12 at 1:28
why are you joining with a comma and then stripping at the comma again?
– Jack Herer
Nov 12 at 1:30
Why are you doing this as raw SQL rather than an ORM query, which would take care of all relevant escaping?
– Daniel Roseman
Nov 12 at 7:37
@DanielRoseman if you can show me how to do it as a regular ORM query I'm all for it -- note there's also a union in the query (not mentioned above) and two limits applied, so not really a straightforward query.
– David L
Nov 12 at 23:48
add a comment |
can you show whatidsis?
– Jack Herer
Nov 12 at 1:26
what exactly are you trying to do? do you want to create a tuple of ids to use as a variable in sql
– Jack Herer
Nov 12 at 1:28
why are you joining with a comma and then stripping at the comma again?
– Jack Herer
Nov 12 at 1:30
Why are you doing this as raw SQL rather than an ORM query, which would take care of all relevant escaping?
– Daniel Roseman
Nov 12 at 7:37
@DanielRoseman if you can show me how to do it as a regular ORM query I'm all for it -- note there's also a union in the query (not mentioned above) and two limits applied, so not really a straightforward query.
– David L
Nov 12 at 23:48
can you show what
ids is?– Jack Herer
Nov 12 at 1:26
can you show what
ids is?– Jack Herer
Nov 12 at 1:26
what exactly are you trying to do? do you want to create a tuple of ids to use as a variable in sql
– Jack Herer
Nov 12 at 1:28
what exactly are you trying to do? do you want to create a tuple of ids to use as a variable in sql
– Jack Herer
Nov 12 at 1:28
why are you joining with a comma and then stripping at the comma again?
– Jack Herer
Nov 12 at 1:30
why are you joining with a comma and then stripping at the comma again?
– Jack Herer
Nov 12 at 1:30
Why are you doing this as raw SQL rather than an ORM query, which would take care of all relevant escaping?
– Daniel Roseman
Nov 12 at 7:37
Why are you doing this as raw SQL rather than an ORM query, which would take care of all relevant escaping?
– Daniel Roseman
Nov 12 at 7:37
@DanielRoseman if you can show me how to do it as a regular ORM query I'm all for it -- note there's also a union in the query (not mentioned above) and two limits applied, so not really a straightforward query.
– David L
Nov 12 at 23:48
@DanielRoseman if you can show me how to do it as a regular ORM query I'm all for it -- note there's also a union in the query (not mentioned above) and two limits applied, so not really a straightforward query.
– David L
Nov 12 at 23:48
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
This might be a really stupid approach, but one way would be to combine the two different types of string formatting as follows:
sql = "SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s) LIMIT 20 UNION SELECT * FROM mturk_imdb WHERE name=%s AND (imdb_id NOT IN %s) LIMIT 30" % ('%s', imdb_ids_as_tuple_str, '%s', imdb_ids_as_tuple_str)
items = list(Item.objects.raw(sql, (q, q)))
Where you need the exact unescaped string, you can use %, and where you want to escape user inputted data, you can use the , (params).
answered Nov 11 at 23:02
David L
35310
add a comment |
Your Answer
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%2f53254077%2fhow-to-escape-quotes-in-django-sql-formatting%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
up vote
0
down vote
This might be a really stupid approach, but one way would be to combine the two different types of string formatting as follows:
sql = "SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s) LIMIT 20 UNION SELECT * FROM mturk_imdb WHERE name=%s AND (imdb_id NOT IN %s) LIMIT 30" % ('%s', imdb_ids_as_tuple_str, '%s', imdb_ids_as_tuple_str)
items = list(Item.objects.raw(sql, (q, q)))
Where you need the exact unescaped string, you can use %, and where you want to escape user inputted data, you can use the , (params).
answered Nov 11 at 23:02
David L
35310
add a comment |
up vote
0
down vote
This might be a really stupid approach, but one way would be to combine the two different types of string formatting as follows:
sql = "SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s) LIMIT 20 UNION SELECT * FROM mturk_imdb WHERE name=%s AND (imdb_id NOT IN %s) LIMIT 30" % ('%s', imdb_ids_as_tuple_str, '%s', imdb_ids_as_tuple_str)
items = list(Item.objects.raw(sql, (q, q)))
Where you need the exact unescaped string, you can use %, and where you want to escape user inputted data, you can use the , (params).
answered Nov 11 at 23:02
David L
35310
add a comment |
up vote
0
down vote
up vote
0
down vote
This might be a really stupid approach, but one way would be to combine the two different types of string formatting as follows:
sql = "SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s) LIMIT 20 UNION SELECT * FROM mturk_imdb WHERE name=%s AND (imdb_id NOT IN %s) LIMIT 30" % ('%s', imdb_ids_as_tuple_str, '%s', imdb_ids_as_tuple_str)
items = list(Item.objects.raw(sql, (q, q)))
Where you need the exact unescaped string, you can use %, and where you want to escape user inputted data, you can use the , (params).
answered Nov 11 at 23:02
David L
35310
This might be a really stupid approach, but one way would be to combine the two different types of string formatting as follows:
sql = "SELECT * FROM mturk_imdb WHERE (MATCH(name) against(%s)) AND (imdb_id NOT IN %s) LIMIT 20 UNION SELECT * FROM mturk_imdb WHERE name=%s AND (imdb_id NOT IN %s) LIMIT 30" % ('%s', imdb_ids_as_tuple_str, '%s', imdb_ids_as_tuple_str)
items = list(Item.objects.raw(sql, (q, q)))
Where you need the exact unescaped string, you can use %, and where you want to escape user inputted data, you can use the , (params).
answered Nov 11 at 23:02
David L
35310
answered Nov 11 at 23:02
David L
35310
answered Nov 11 at 23:02
David L
35310
answered Nov 11 at 23:02
David L
35310
35310
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53254077%2fhow-to-escape-quotes-in-django-sql-formatting%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
can you show what
idsis?– Jack Herer
Nov 12 at 1:26
what exactly are you trying to do? do you want to create a tuple of ids to use as a variable in sql
– Jack Herer
Nov 12 at 1:28
why are you joining with a comma and then stripping at the comma again?
– Jack Herer
Nov 12 at 1:30
Why are you doing this as raw SQL rather than an ORM query, which would take care of all relevant escaping?
– Daniel Roseman
Nov 12 at 7:37
@DanielRoseman if you can show me how to do it as a regular ORM query I'm all for it -- note there's also a union in the query (not mentioned above) and two limits applied, so not really a straightforward query.
– David L
Nov 12 at 23:48