Mixing
Python cursor.execute() with MySQL UPDATE causes syntax error
Following this example I am attempting to rewrite code that works with code that protects against SQL injection:
Code that works:
table = "led_status"
field = "test_led"
value = "FALSE"
cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))
code that does not work:
table = "led_status"
field = "test_led"
value = "FALSE"
cursor.execute(("UPDATE %s SET %s = %s", table, field, value))
Nor does this code work:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update_command = "UPDATE %s SET %s = %s"
cursor.execute(sql_update_command, (table, field, value))
The first example works, the others do not and each of them throw this syntax error:
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''led_status' SET 'test_led' = 'FALSE'' at line 1
I am not sure what I am doing wrong so any pointers would be greatly appreciated.
python mysql sql-injection
asked Jan 3 at 1:06
RichardRichard
16118
add a comment |
Following this example I am attempting to rewrite code that works with code that protects against SQL injection:
Code that works:
table = "led_status"
field = "test_led"
value = "FALSE"
cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))
code that does not work:
table = "led_status"
field = "test_led"
value = "FALSE"
cursor.execute(("UPDATE %s SET %s = %s", table, field, value))
Nor does this code work:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update_command = "UPDATE %s SET %s = %s"
cursor.execute(sql_update_command, (table, field, value))
The first example works, the others do not and each of them throw this syntax error:
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''led_status' SET 'test_led' = 'FALSE'' at line 1
I am not sure what I am doing wrong so any pointers would be greatly appreciated.
python mysql sql-injection
asked Jan 3 at 1:06
RichardRichard
16118
add a comment |
Following this example I am attempting to rewrite code that works with code that protects against SQL injection:
Code that works:
table = "led_status"
field = "test_led"
value = "FALSE"
cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))
code that does not work:
table = "led_status"
field = "test_led"
value = "FALSE"
cursor.execute(("UPDATE %s SET %s = %s", table, field, value))
Nor does this code work:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update_command = "UPDATE %s SET %s = %s"
cursor.execute(sql_update_command, (table, field, value))
The first example works, the others do not and each of them throw this syntax error:
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''led_status' SET 'test_led' = 'FALSE'' at line 1
I am not sure what I am doing wrong so any pointers would be greatly appreciated.
python mysql sql-injection
asked Jan 3 at 1:06
RichardRichard
16118
Following this example I am attempting to rewrite code that works with code that protects against SQL injection:
Code that works:
table = "led_status"
field = "test_led"
value = "FALSE"
cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))
code that does not work:
table = "led_status"
field = "test_led"
value = "FALSE"
cursor.execute(("UPDATE %s SET %s = %s", table, field, value))
Nor does this code work:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update_command = "UPDATE %s SET %s = %s"
cursor.execute(sql_update_command, (table, field, value))
The first example works, the others do not and each of them throw this syntax error:
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''led_status' SET 'test_led' = 'FALSE'' at line 1
I am not sure what I am doing wrong so any pointers would be greatly appreciated.
python mysql sql-injection
python mysql sql-injection
asked Jan 3 at 1:06
RichardRichard
16118
asked Jan 3 at 1:06
RichardRichard
16118
edited Jan 3 at 1:11
Richard
asked Jan 3 at 1:06
RichardRichard
16118
asked Jan 3 at 1:06
RichardRichard
16118
asked Jan 3 at 1:06
RichardRichard
16118
16118
add a comment |
add a comment |
5 Answers
5
active
oldest
votes
The optimal way for doing it according to the doccummentation is with:
from psycopg2 import sql
cur.execute(
sql.SQL("insert into {} values (%s, %s)")
.format(sql.Identifier('my_table')),
[10, 20])
Source
answered Jan 4 at 20:06
Bruno CarballoBruno Carballo
49629
add a comment |
I like to use psycopg2 for instances like this where you are trying to feed a column name as an input and do not want it escaped
from psycopg2.extensions import AsIs
cmd = """
UPDATE %(table)s SET %(column)s = %(val)s
"""
kwargs = {
'table': AsIs('led_status'),
'column': AsIs('test_led'),
'val': False
}
cursor.execute(cmd, kwargs)
answered Jan 3 at 1:46
aws_apprenticeaws_apprentice
3,8802723
add a comment |
Your SQL is incorrect in all 3 examples.
The following code should work:
table = "led_status"
field = "test_led"
value = False
cursor.execute("UPDATE %s SET `%s` = %s", (table, field, value))
Notice the backticks (`) around table and column names, however, values should be represented as the columns respective object type. Single and double quotes should be used to represent string values.
In your case, FALSE should most likely not be stored as a string, but a boolean, or tinyint within your database schema.
answered Jan 3 at 1:18
StephanStephan
164210
Actually, in my case, I want FALSE to be stored as a literal string. I'll try the other changes!
– Richard
Jan 3 at 1:24
hummm...still didn't work, now I am getting:AttributeError: 'tuple' object has no attribute 'encode'
– Richard
Jan 3 at 1:26
add a comment |
Thanks for the tips. Eventually I figured out my error after reading this post. Apparently you can't parameterize a table name !
This is how I fixed the code:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update = "UPDATE " + table + " SET " + column + " = %s"
cursor.execute(sql_update, (value,))
answered Jan 3 at 3:38
RichardRichard
16118
You don't have a prepared statement. The last example was updated as the tuples were surrounding the entire statement, and I did not see it at first glance.
– Stephan
Jan 3 at 15:33
updated reply...
– Richard
Jan 4 at 19:53
To pass the table name you sould usesql.Identifierandformat()
– Bruno Carballo
Jan 4 at 20:08
add a comment |
Since your question is about SQL injection, I'll go a little further than giving you a one liner. Your problem is that you cannot parameterize a table name. When dealing with SQL injection you have to distinguish the difference between dynamic SQL and user input values. Escaping values to protect against SQL injection should be handled by the driver's quote/escaping mechanism. You are on your own validating dynamic SQL fragments. Dynamic SQL fragments include things like variable WHERE condition expressions, variable table names or variable SELECT field lists.
The correct syntax for your example is:
cursor.execute("UPDATE {} SET {} = %s".format(table, field), value)
or
cursor.execute("UPDATE %s SET %s = %%s" % (table, field), value)
However, if the table does come from the user you must validate it against some predefined list before you get to this statement. You cannot trust the database to do the right thing with table names or field names. The following data structure would provide something to validate off of for example.
valid_fields = {
'table_1': ['field_1', 'field_2'],
'table_2': ['field_a', 'field_b'],
'table_3': ['field_x', 'field_y']
}
You can also use special catalog tables your database provides (like pg_catalog) to get these dynamically, but you should still check for special field/table names (OID for example).
answered Jan 4 at 20:24
JeremyJeremy
5551612
Thanks for the extra information. From a programming view I want to protect against injection however the values for table, column, etc all come hardcoded from various other functions and passed as a variable to a single database function to write an update that words program wide, there is no user interaction or input at all. However I did try both of your examples and both of them failed with:1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
– Richard
Jan 6 at 18:27
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%2f54015124%2fpython-cursor-execute-with-mysql-update-causes-syntax-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
The optimal way for doing it according to the doccummentation is with:
from psycopg2 import sql
cur.execute(
sql.SQL("insert into {} values (%s, %s)")
.format(sql.Identifier('my_table')),
[10, 20])
Source
answered Jan 4 at 20:06
Bruno CarballoBruno Carballo
49629
add a comment |
The optimal way for doing it according to the doccummentation is with:
from psycopg2 import sql
cur.execute(
sql.SQL("insert into {} values (%s, %s)")
.format(sql.Identifier('my_table')),
[10, 20])
Source
answered Jan 4 at 20:06
Bruno CarballoBruno Carballo
49629
add a comment |
The optimal way for doing it according to the doccummentation is with:
from psycopg2 import sql
cur.execute(
sql.SQL("insert into {} values (%s, %s)")
.format(sql.Identifier('my_table')),
[10, 20])
Source
answered Jan 4 at 20:06
Bruno CarballoBruno Carballo
49629
The optimal way for doing it according to the doccummentation is with:
from psycopg2 import sql
cur.execute(
sql.SQL("insert into {} values (%s, %s)")
.format(sql.Identifier('my_table')),
[10, 20])
Source
answered Jan 4 at 20:06
Bruno CarballoBruno Carballo
49629
answered Jan 4 at 20:06
Bruno CarballoBruno Carballo
49629
answered Jan 4 at 20:06
Bruno CarballoBruno Carballo
49629
answered Jan 4 at 20:06
Bruno CarballoBruno Carballo
49629
49629
add a comment |
add a comment |
I like to use psycopg2 for instances like this where you are trying to feed a column name as an input and do not want it escaped
from psycopg2.extensions import AsIs
cmd = """
UPDATE %(table)s SET %(column)s = %(val)s
"""
kwargs = {
'table': AsIs('led_status'),
'column': AsIs('test_led'),
'val': False
}
cursor.execute(cmd, kwargs)
answered Jan 3 at 1:46
aws_apprenticeaws_apprentice
3,8802723
add a comment |
I like to use psycopg2 for instances like this where you are trying to feed a column name as an input and do not want it escaped
from psycopg2.extensions import AsIs
cmd = """
UPDATE %(table)s SET %(column)s = %(val)s
"""
kwargs = {
'table': AsIs('led_status'),
'column': AsIs('test_led'),
'val': False
}
cursor.execute(cmd, kwargs)
answered Jan 3 at 1:46
aws_apprenticeaws_apprentice
3,8802723
add a comment |
I like to use psycopg2 for instances like this where you are trying to feed a column name as an input and do not want it escaped
from psycopg2.extensions import AsIs
cmd = """
UPDATE %(table)s SET %(column)s = %(val)s
"""
kwargs = {
'table': AsIs('led_status'),
'column': AsIs('test_led'),
'val': False
}
cursor.execute(cmd, kwargs)
answered Jan 3 at 1:46
aws_apprenticeaws_apprentice
3,8802723
I like to use psycopg2 for instances like this where you are trying to feed a column name as an input and do not want it escaped
from psycopg2.extensions import AsIs
cmd = """
UPDATE %(table)s SET %(column)s = %(val)s
"""
kwargs = {
'table': AsIs('led_status'),
'column': AsIs('test_led'),
'val': False
}
cursor.execute(cmd, kwargs)
answered Jan 3 at 1:46
aws_apprenticeaws_apprentice
3,8802723
answered Jan 3 at 1:46
aws_apprenticeaws_apprentice
3,8802723
answered Jan 3 at 1:46
aws_apprenticeaws_apprentice
3,8802723
answered Jan 3 at 1:46
aws_apprenticeaws_apprentice
3,8802723
3,8802723
add a comment |
add a comment |
Your SQL is incorrect in all 3 examples.
The following code should work:
table = "led_status"
field = "test_led"
value = False
cursor.execute("UPDATE %s SET `%s` = %s", (table, field, value))
Notice the backticks (`) around table and column names, however, values should be represented as the columns respective object type. Single and double quotes should be used to represent string values.
In your case, FALSE should most likely not be stored as a string, but a boolean, or tinyint within your database schema.
answered Jan 3 at 1:18
StephanStephan
164210
Actually, in my case, I want FALSE to be stored as a literal string. I'll try the other changes!
– Richard
Jan 3 at 1:24
hummm...still didn't work, now I am getting:AttributeError: 'tuple' object has no attribute 'encode'
– Richard
Jan 3 at 1:26
add a comment |
Your SQL is incorrect in all 3 examples.
The following code should work:
table = "led_status"
field = "test_led"
value = False
cursor.execute("UPDATE %s SET `%s` = %s", (table, field, value))
Notice the backticks (`) around table and column names, however, values should be represented as the columns respective object type. Single and double quotes should be used to represent string values.
In your case, FALSE should most likely not be stored as a string, but a boolean, or tinyint within your database schema.
answered Jan 3 at 1:18
StephanStephan
164210
Actually, in my case, I want FALSE to be stored as a literal string. I'll try the other changes!
– Richard
Jan 3 at 1:24
hummm...still didn't work, now I am getting:AttributeError: 'tuple' object has no attribute 'encode'
– Richard
Jan 3 at 1:26
add a comment |
Your SQL is incorrect in all 3 examples.
The following code should work:
table = "led_status"
field = "test_led"
value = False
cursor.execute("UPDATE %s SET `%s` = %s", (table, field, value))
Notice the backticks (`) around table and column names, however, values should be represented as the columns respective object type. Single and double quotes should be used to represent string values.
In your case, FALSE should most likely not be stored as a string, but a boolean, or tinyint within your database schema.
answered Jan 3 at 1:18
StephanStephan
164210
Your SQL is incorrect in all 3 examples.
The following code should work:
table = "led_status"
field = "test_led"
value = False
cursor.execute("UPDATE %s SET `%s` = %s", (table, field, value))
Notice the backticks (`) around table and column names, however, values should be represented as the columns respective object type. Single and double quotes should be used to represent string values.
In your case, FALSE should most likely not be stored as a string, but a boolean, or tinyint within your database schema.
answered Jan 3 at 1:18
StephanStephan
164210
edited Jan 3 at 15:32
answered Jan 3 at 1:18
StephanStephan
164210
answered Jan 3 at 1:18
StephanStephan
164210
answered Jan 3 at 1:18
StephanStephan
164210
164210
Actually, in my case, I want FALSE to be stored as a literal string. I'll try the other changes!
– Richard
Jan 3 at 1:24
hummm...still didn't work, now I am getting:AttributeError: 'tuple' object has no attribute 'encode'
– Richard
Jan 3 at 1:26
add a comment |
Actually, in my case, I want FALSE to be stored as a literal string. I'll try the other changes!
– Richard
Jan 3 at 1:24
hummm...still didn't work, now I am getting:AttributeError: 'tuple' object has no attribute 'encode'
– Richard
Jan 3 at 1:26
Actually, in my case, I want FALSE to be stored as a literal string. I'll try the other changes!
– Richard
Jan 3 at 1:24
Actually, in my case, I want FALSE to be stored as a literal string. I'll try the other changes!
– Richard
Jan 3 at 1:24
hummm...still didn't work, now I am getting:
AttributeError: 'tuple' object has no attribute 'encode'– Richard
Jan 3 at 1:26
hummm...still didn't work, now I am getting:
AttributeError: 'tuple' object has no attribute 'encode'– Richard
Jan 3 at 1:26
add a comment |
Thanks for the tips. Eventually I figured out my error after reading this post. Apparently you can't parameterize a table name !
This is how I fixed the code:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update = "UPDATE " + table + " SET " + column + " = %s"
cursor.execute(sql_update, (value,))
answered Jan 3 at 3:38
RichardRichard
16118
You don't have a prepared statement. The last example was updated as the tuples were surrounding the entire statement, and I did not see it at first glance.
– Stephan
Jan 3 at 15:33
updated reply...
– Richard
Jan 4 at 19:53
To pass the table name you sould usesql.Identifierandformat()
– Bruno Carballo
Jan 4 at 20:08
add a comment |
Thanks for the tips. Eventually I figured out my error after reading this post. Apparently you can't parameterize a table name !
This is how I fixed the code:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update = "UPDATE " + table + " SET " + column + " = %s"
cursor.execute(sql_update, (value,))
answered Jan 3 at 3:38
RichardRichard
16118
You don't have a prepared statement. The last example was updated as the tuples were surrounding the entire statement, and I did not see it at first glance.
– Stephan
Jan 3 at 15:33
updated reply...
– Richard
Jan 4 at 19:53
To pass the table name you sould usesql.Identifierandformat()
– Bruno Carballo
Jan 4 at 20:08
add a comment |
Thanks for the tips. Eventually I figured out my error after reading this post. Apparently you can't parameterize a table name !
This is how I fixed the code:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update = "UPDATE " + table + " SET " + column + " = %s"
cursor.execute(sql_update, (value,))
answered Jan 3 at 3:38
RichardRichard
16118
Thanks for the tips. Eventually I figured out my error after reading this post. Apparently you can't parameterize a table name !
This is how I fixed the code:
table = "led_status"
field = "test_led"
value = "FALSE"
sql_update = "UPDATE " + table + " SET " + column + " = %s"
cursor.execute(sql_update, (value,))
answered Jan 3 at 3:38
RichardRichard
16118
edited Jan 4 at 19:53
answered Jan 3 at 3:38
RichardRichard
16118
answered Jan 3 at 3:38
RichardRichard
16118
answered Jan 3 at 3:38
RichardRichard
16118
16118
You don't have a prepared statement. The last example was updated as the tuples were surrounding the entire statement, and I did not see it at first glance.
– Stephan
Jan 3 at 15:33
updated reply...
– Richard
Jan 4 at 19:53
To pass the table name you sould usesql.Identifierandformat()
– Bruno Carballo
Jan 4 at 20:08
add a comment |
You don't have a prepared statement. The last example was updated as the tuples were surrounding the entire statement, and I did not see it at first glance.
– Stephan
Jan 3 at 15:33
updated reply...
– Richard
Jan 4 at 19:53
To pass the table name you sould usesql.Identifierandformat()
– Bruno Carballo
Jan 4 at 20:08
You don't have a prepared statement. The last example was updated as the tuples were surrounding the entire statement, and I did not see it at first glance.
– Stephan
Jan 3 at 15:33
You don't have a prepared statement. The last example was updated as the tuples were surrounding the entire statement, and I did not see it at first glance.
– Stephan
Jan 3 at 15:33
updated reply...
– Richard
Jan 4 at 19:53
updated reply...
– Richard
Jan 4 at 19:53
To pass the table name you sould use
sql.Identifier and format()– Bruno Carballo
Jan 4 at 20:08
To pass the table name you sould use
sql.Identifier and format()– Bruno Carballo
Jan 4 at 20:08
add a comment |
Since your question is about SQL injection, I'll go a little further than giving you a one liner. Your problem is that you cannot parameterize a table name. When dealing with SQL injection you have to distinguish the difference between dynamic SQL and user input values. Escaping values to protect against SQL injection should be handled by the driver's quote/escaping mechanism. You are on your own validating dynamic SQL fragments. Dynamic SQL fragments include things like variable WHERE condition expressions, variable table names or variable SELECT field lists.
The correct syntax for your example is:
cursor.execute("UPDATE {} SET {} = %s".format(table, field), value)
or
cursor.execute("UPDATE %s SET %s = %%s" % (table, field), value)
However, if the table does come from the user you must validate it against some predefined list before you get to this statement. You cannot trust the database to do the right thing with table names or field names. The following data structure would provide something to validate off of for example.
valid_fields = {
'table_1': ['field_1', 'field_2'],
'table_2': ['field_a', 'field_b'],
'table_3': ['field_x', 'field_y']
}
You can also use special catalog tables your database provides (like pg_catalog) to get these dynamically, but you should still check for special field/table names (OID for example).
answered Jan 4 at 20:24
JeremyJeremy
5551612
Thanks for the extra information. From a programming view I want to protect against injection however the values for table, column, etc all come hardcoded from various other functions and passed as a variable to a single database function to write an update that words program wide, there is no user interaction or input at all. However I did try both of your examples and both of them failed with:1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
– Richard
Jan 6 at 18:27
add a comment |
Since your question is about SQL injection, I'll go a little further than giving you a one liner. Your problem is that you cannot parameterize a table name. When dealing with SQL injection you have to distinguish the difference between dynamic SQL and user input values. Escaping values to protect against SQL injection should be handled by the driver's quote/escaping mechanism. You are on your own validating dynamic SQL fragments. Dynamic SQL fragments include things like variable WHERE condition expressions, variable table names or variable SELECT field lists.
The correct syntax for your example is:
cursor.execute("UPDATE {} SET {} = %s".format(table, field), value)
or
cursor.execute("UPDATE %s SET %s = %%s" % (table, field), value)
However, if the table does come from the user you must validate it against some predefined list before you get to this statement. You cannot trust the database to do the right thing with table names or field names. The following data structure would provide something to validate off of for example.
valid_fields = {
'table_1': ['field_1', 'field_2'],
'table_2': ['field_a', 'field_b'],
'table_3': ['field_x', 'field_y']
}
You can also use special catalog tables your database provides (like pg_catalog) to get these dynamically, but you should still check for special field/table names (OID for example).
answered Jan 4 at 20:24
JeremyJeremy
5551612
Thanks for the extra information. From a programming view I want to protect against injection however the values for table, column, etc all come hardcoded from various other functions and passed as a variable to a single database function to write an update that words program wide, there is no user interaction or input at all. However I did try both of your examples and both of them failed with:1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
– Richard
Jan 6 at 18:27
add a comment |
Since your question is about SQL injection, I'll go a little further than giving you a one liner. Your problem is that you cannot parameterize a table name. When dealing with SQL injection you have to distinguish the difference between dynamic SQL and user input values. Escaping values to protect against SQL injection should be handled by the driver's quote/escaping mechanism. You are on your own validating dynamic SQL fragments. Dynamic SQL fragments include things like variable WHERE condition expressions, variable table names or variable SELECT field lists.
The correct syntax for your example is:
cursor.execute("UPDATE {} SET {} = %s".format(table, field), value)
or
cursor.execute("UPDATE %s SET %s = %%s" % (table, field), value)
However, if the table does come from the user you must validate it against some predefined list before you get to this statement. You cannot trust the database to do the right thing with table names or field names. The following data structure would provide something to validate off of for example.
valid_fields = {
'table_1': ['field_1', 'field_2'],
'table_2': ['field_a', 'field_b'],
'table_3': ['field_x', 'field_y']
}
You can also use special catalog tables your database provides (like pg_catalog) to get these dynamically, but you should still check for special field/table names (OID for example).
answered Jan 4 at 20:24
JeremyJeremy
5551612
Since your question is about SQL injection, I'll go a little further than giving you a one liner. Your problem is that you cannot parameterize a table name. When dealing with SQL injection you have to distinguish the difference between dynamic SQL and user input values. Escaping values to protect against SQL injection should be handled by the driver's quote/escaping mechanism. You are on your own validating dynamic SQL fragments. Dynamic SQL fragments include things like variable WHERE condition expressions, variable table names or variable SELECT field lists.
The correct syntax for your example is:
cursor.execute("UPDATE {} SET {} = %s".format(table, field), value)
or
cursor.execute("UPDATE %s SET %s = %%s" % (table, field), value)
However, if the table does come from the user you must validate it against some predefined list before you get to this statement. You cannot trust the database to do the right thing with table names or field names. The following data structure would provide something to validate off of for example.
valid_fields = {
'table_1': ['field_1', 'field_2'],
'table_2': ['field_a', 'field_b'],
'table_3': ['field_x', 'field_y']
}
You can also use special catalog tables your database provides (like pg_catalog) to get these dynamically, but you should still check for special field/table names (OID for example).
answered Jan 4 at 20:24
JeremyJeremy
5551612
answered Jan 4 at 20:24
JeremyJeremy
5551612
answered Jan 4 at 20:24
JeremyJeremy
5551612
answered Jan 4 at 20:24
JeremyJeremy
5551612
5551612
Thanks for the extra information. From a programming view I want to protect against injection however the values for table, column, etc all come hardcoded from various other functions and passed as a variable to a single database function to write an update that words program wide, there is no user interaction or input at all. However I did try both of your examples and both of them failed with:1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
– Richard
Jan 6 at 18:27
add a comment |
Thanks for the extra information. From a programming view I want to protect against injection however the values for table, column, etc all come hardcoded from various other functions and passed as a variable to a single database function to write an update that words program wide, there is no user interaction or input at all. However I did try both of your examples and both of them failed with:1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
– Richard
Jan 6 at 18:27
Thanks for the extra information. From a programming view I want to protect against injection however the values for table, column, etc all come hardcoded from various other functions and passed as a variable to a single database function to write an update that words program wide, there is no user interaction or input at all. However I did try both of your examples and both of them failed with:
1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version– Richard
Jan 6 at 18:27
Thanks for the extra information. From a programming view I want to protect against injection however the values for table, column, etc all come hardcoded from various other functions and passed as a variable to a single database function to write an update that words program wide, there is no user interaction or input at all. However I did try both of your examples and both of them failed with:
1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version– Richard
Jan 6 at 18:27
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%2f54015124%2fpython-cursor-execute-with-mysql-update-causes-syntax-error%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
