SQL Query returning “None” instead of row
I'm working on a light login, and have a tabled titled Users. I'm trying to take my login form POST body and verify it across the database.
Values from form:
user = request.form['username']
password = request.form['password']
SQL Statement:
conn = sqlite3.connect(db)
cur = conn.cursor()
cur.execute("SELECT * FROM Users WHERE Username LIKE '(%s)'" % user)
row = cur.fetchone()
Users Table:
So on a POST request from my form, here is what is printed:
Print(user, password) = ph104694 Password123
Print(row) = None
So you can see the row is being returned as None when the data absolutely exists. If I change user to something I know is incorrect, I'm getting the same results, but if I change the table from Users to something like Users2 I'm met with a no table exists error which is fine. So despite matching data existing something about my statement isn't allowing it to produce that row. Any ideas?
python sql python-3.x sqlite
asked Nov 26 '18 at 0:22
CodeSpentCodeSpent
715619
add a comment |
I'm working on a light login, and have a tabled titled Users. I'm trying to take my login form POST body and verify it across the database.
Values from form:
user = request.form['username']
password = request.form['password']
SQL Statement:
conn = sqlite3.connect(db)
cur = conn.cursor()
cur.execute("SELECT * FROM Users WHERE Username LIKE '(%s)'" % user)
row = cur.fetchone()
Users Table:
So on a POST request from my form, here is what is printed:
Print(user, password) = ph104694 Password123
Print(row) = None
So you can see the row is being returned as None when the data absolutely exists. If I change user to something I know is incorrect, I'm getting the same results, but if I change the table from Users to something like Users2 I'm met with a no table exists error which is fine. So despite matching data existing something about my statement isn't allowing it to produce that row. Any ideas?
python sql python-3.x sqlite
asked Nov 26 '18 at 0:22
CodeSpentCodeSpent
715619
add a comment |
I'm working on a light login, and have a tabled titled Users. I'm trying to take my login form POST body and verify it across the database.
Values from form:
user = request.form['username']
password = request.form['password']
SQL Statement:
conn = sqlite3.connect(db)
cur = conn.cursor()
cur.execute("SELECT * FROM Users WHERE Username LIKE '(%s)'" % user)
row = cur.fetchone()
Users Table:
So on a POST request from my form, here is what is printed:
Print(user, password) = ph104694 Password123
Print(row) = None
So you can see the row is being returned as None when the data absolutely exists. If I change user to something I know is incorrect, I'm getting the same results, but if I change the table from Users to something like Users2 I'm met with a no table exists error which is fine. So despite matching data existing something about my statement isn't allowing it to produce that row. Any ideas?
python sql python-3.x sqlite
asked Nov 26 '18 at 0:22
CodeSpentCodeSpent
715619
I'm working on a light login, and have a tabled titled Users. I'm trying to take my login form POST body and verify it across the database.
Values from form:
user = request.form['username']
password = request.form['password']
SQL Statement:
conn = sqlite3.connect(db)
cur = conn.cursor()
cur.execute("SELECT * FROM Users WHERE Username LIKE '(%s)'" % user)
row = cur.fetchone()
Users Table:
So on a POST request from my form, here is what is printed:
Print(user, password) = ph104694 Password123
Print(row) = None
So you can see the row is being returned as None when the data absolutely exists. If I change user to something I know is incorrect, I'm getting the same results, but if I change the table from Users to something like Users2 I'm met with a no table exists error which is fine. So despite matching data existing something about my statement isn't allowing it to produce that row. Any ideas?
python sql python-3.x sqlite
python sql python-3.x sqlite
asked Nov 26 '18 at 0:22
CodeSpentCodeSpent
715619
asked Nov 26 '18 at 0:22
CodeSpentCodeSpent
715619
asked Nov 26 '18 at 0:22
CodeSpentCodeSpent
715619
asked Nov 26 '18 at 0:22
CodeSpentCodeSpent
715619
asked Nov 26 '18 at 0:22
CodeSpentCodeSpent
715619
715619
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You're search expression is evaluating to
(ph104694)which clearly doesn't exist in the data you showed.There is no reason to use the LIKE operator here and it probably runs counter to what you want to do (match the single record exactly matching the user ID that was entered).
This is the classic example of code that is subject to an SQL injection attack. You should never, never, ever use string interpolation to build an SQL string like this. Instead, use parameter substitution.
Taken all together, you want something like this:
cur.execute("SELECT * FROM Users WHERE Username = ?", [user])
answered Nov 26 '18 at 1:49
Larry LustigLarry Lustig
40.3k1284130
Thanks for the tips, so I've found out. Either way none of my SQL will ever be used anyways, so just needed to get over the hump. ;) I'll accept this answer if the gentleman who helped me solve the issue below doesn't update their answer to reflect the issue/solution.
– CodeSpent
Nov 26 '18 at 1:54
add a comment |
Your query string evaluates to "SELECT * FROM Users WHERE Username LIKE '(ph104694)'".
Note the parentheses which aren't in the actual username.
Also, you almost certainly don't want to use LIKE.
What you want is "SELECT * FROM Users WHERE Username = 'ph104694'"
Which would create with "SELECT * FROM Users WHERE Username = '{user}'".format(user=user)
Also, you can (and should) parameterize this as
cur.execute("SELECT * FROM Users WHERE Username = :user", {user: user})
answered Nov 26 '18 at 1:41
BatmanBatman
4,62531651
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%2f53473365%2fsql-query-returning-none-instead-of-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You're search expression is evaluating to
(ph104694)which clearly doesn't exist in the data you showed.There is no reason to use the LIKE operator here and it probably runs counter to what you want to do (match the single record exactly matching the user ID that was entered).
This is the classic example of code that is subject to an SQL injection attack. You should never, never, ever use string interpolation to build an SQL string like this. Instead, use parameter substitution.
Taken all together, you want something like this:
cur.execute("SELECT * FROM Users WHERE Username = ?", [user])
answered Nov 26 '18 at 1:49
Larry LustigLarry Lustig
40.3k1284130
Thanks for the tips, so I've found out. Either way none of my SQL will ever be used anyways, so just needed to get over the hump. ;) I'll accept this answer if the gentleman who helped me solve the issue below doesn't update their answer to reflect the issue/solution.
– CodeSpent
Nov 26 '18 at 1:54
add a comment |
You're search expression is evaluating to
(ph104694)which clearly doesn't exist in the data you showed.There is no reason to use the LIKE operator here and it probably runs counter to what you want to do (match the single record exactly matching the user ID that was entered).
This is the classic example of code that is subject to an SQL injection attack. You should never, never, ever use string interpolation to build an SQL string like this. Instead, use parameter substitution.
Taken all together, you want something like this:
cur.execute("SELECT * FROM Users WHERE Username = ?", [user])
answered Nov 26 '18 at 1:49
Larry LustigLarry Lustig
40.3k1284130
Thanks for the tips, so I've found out. Either way none of my SQL will ever be used anyways, so just needed to get over the hump. ;) I'll accept this answer if the gentleman who helped me solve the issue below doesn't update their answer to reflect the issue/solution.
– CodeSpent
Nov 26 '18 at 1:54
add a comment |
You're search expression is evaluating to
(ph104694)which clearly doesn't exist in the data you showed.There is no reason to use the LIKE operator here and it probably runs counter to what you want to do (match the single record exactly matching the user ID that was entered).
This is the classic example of code that is subject to an SQL injection attack. You should never, never, ever use string interpolation to build an SQL string like this. Instead, use parameter substitution.
Taken all together, you want something like this:
cur.execute("SELECT * FROM Users WHERE Username = ?", [user])
answered Nov 26 '18 at 1:49
Larry LustigLarry Lustig
40.3k1284130
You're search expression is evaluating to
(ph104694)which clearly doesn't exist in the data you showed.There is no reason to use the LIKE operator here and it probably runs counter to what you want to do (match the single record exactly matching the user ID that was entered).
This is the classic example of code that is subject to an SQL injection attack. You should never, never, ever use string interpolation to build an SQL string like this. Instead, use parameter substitution.
Taken all together, you want something like this:
cur.execute("SELECT * FROM Users WHERE Username = ?", [user])
answered Nov 26 '18 at 1:49
Larry LustigLarry Lustig
40.3k1284130
answered Nov 26 '18 at 1:49
Larry LustigLarry Lustig
40.3k1284130
answered Nov 26 '18 at 1:49
Larry LustigLarry Lustig
40.3k1284130
answered Nov 26 '18 at 1:49
Larry LustigLarry Lustig
40.3k1284130
40.3k1284130
Thanks for the tips, so I've found out. Either way none of my SQL will ever be used anyways, so just needed to get over the hump. ;) I'll accept this answer if the gentleman who helped me solve the issue below doesn't update their answer to reflect the issue/solution.
– CodeSpent
Nov 26 '18 at 1:54
add a comment |
Thanks for the tips, so I've found out. Either way none of my SQL will ever be used anyways, so just needed to get over the hump. ;) I'll accept this answer if the gentleman who helped me solve the issue below doesn't update their answer to reflect the issue/solution.
– CodeSpent
Nov 26 '18 at 1:54
Thanks for the tips, so I've found out. Either way none of my SQL will ever be used anyways, so just needed to get over the hump. ;) I'll accept this answer if the gentleman who helped me solve the issue below doesn't update their answer to reflect the issue/solution.
– CodeSpent
Nov 26 '18 at 1:54
Thanks for the tips, so I've found out. Either way none of my SQL will ever be used anyways, so just needed to get over the hump. ;) I'll accept this answer if the gentleman who helped me solve the issue below doesn't update their answer to reflect the issue/solution.
– CodeSpent
Nov 26 '18 at 1:54
add a comment |
Your query string evaluates to "SELECT * FROM Users WHERE Username LIKE '(ph104694)'".
Note the parentheses which aren't in the actual username.
Also, you almost certainly don't want to use LIKE.
What you want is "SELECT * FROM Users WHERE Username = 'ph104694'"
Which would create with "SELECT * FROM Users WHERE Username = '{user}'".format(user=user)
Also, you can (and should) parameterize this as
cur.execute("SELECT * FROM Users WHERE Username = :user", {user: user})
answered Nov 26 '18 at 1:41
BatmanBatman
4,62531651
add a comment |
Your query string evaluates to "SELECT * FROM Users WHERE Username LIKE '(ph104694)'".
Note the parentheses which aren't in the actual username.
Also, you almost certainly don't want to use LIKE.
What you want is "SELECT * FROM Users WHERE Username = 'ph104694'"
Which would create with "SELECT * FROM Users WHERE Username = '{user}'".format(user=user)
Also, you can (and should) parameterize this as
cur.execute("SELECT * FROM Users WHERE Username = :user", {user: user})
answered Nov 26 '18 at 1:41
BatmanBatman
4,62531651
add a comment |
Your query string evaluates to "SELECT * FROM Users WHERE Username LIKE '(ph104694)'".
Note the parentheses which aren't in the actual username.
Also, you almost certainly don't want to use LIKE.
What you want is "SELECT * FROM Users WHERE Username = 'ph104694'"
Which would create with "SELECT * FROM Users WHERE Username = '{user}'".format(user=user)
Also, you can (and should) parameterize this as
cur.execute("SELECT * FROM Users WHERE Username = :user", {user: user})
answered Nov 26 '18 at 1:41
BatmanBatman
4,62531651
Your query string evaluates to "SELECT * FROM Users WHERE Username LIKE '(ph104694)'".
Note the parentheses which aren't in the actual username.
Also, you almost certainly don't want to use LIKE.
What you want is "SELECT * FROM Users WHERE Username = 'ph104694'"
Which would create with "SELECT * FROM Users WHERE Username = '{user}'".format(user=user)
Also, you can (and should) parameterize this as
cur.execute("SELECT * FROM Users WHERE Username = :user", {user: user})
answered Nov 26 '18 at 1:41
BatmanBatman
4,62531651
answered Nov 26 '18 at 1:41
BatmanBatman
4,62531651
answered Nov 26 '18 at 1:41
BatmanBatman
4,62531651
answered Nov 26 '18 at 1:41
BatmanBatman
4,62531651
4,62531651
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.
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%2f53473365%2fsql-query-returning-none-instead-of-row%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
