Add variable columns to INSERT statement
Clash Royale CLAN TAG#URR8PPP
$begingroup$
I'm learning SQL using python library pymysql
.
I wanted to make an INSERT statement that will update 3, 5 or 10 columns using the same code by just passing the Database name, the Columns name and the Values.
Thus I made this:
def insert_statement(db,cols,values):
separator = ","
separator.join(cols)
statement = "INSERT INTO " + db + " (" + separator.join(cols) + ") VALUES ("
aux =
for i in range(0,len(values)):
aux.append("%s")
statement = statement + separator.join(aux) + ")"
print (statement)
return statement
Passing the values, the function produces:
>>>INSERT INTO Publicaciones (item_id,title,price) VALUES (%s,%s,%s)
Which works but, is it there a more pythonic way?
python python-3.x sql
$endgroup$
add a comment |
$begingroup$
I'm learning SQL using python library pymysql
.
I wanted to make an INSERT statement that will update 3, 5 or 10 columns using the same code by just passing the Database name, the Columns name and the Values.
Thus I made this:
def insert_statement(db,cols,values):
separator = ","
separator.join(cols)
statement = "INSERT INTO " + db + " (" + separator.join(cols) + ") VALUES ("
aux =
for i in range(0,len(values)):
aux.append("%s")
statement = statement + separator.join(aux) + ")"
print (statement)
return statement
Passing the values, the function produces:
>>>INSERT INTO Publicaciones (item_id,title,price) VALUES (%s,%s,%s)
Which works but, is it there a more pythonic way?
python python-3.x sql
$endgroup$
add a comment |
$begingroup$
I'm learning SQL using python library pymysql
.
I wanted to make an INSERT statement that will update 3, 5 or 10 columns using the same code by just passing the Database name, the Columns name and the Values.
Thus I made this:
def insert_statement(db,cols,values):
separator = ","
separator.join(cols)
statement = "INSERT INTO " + db + " (" + separator.join(cols) + ") VALUES ("
aux =
for i in range(0,len(values)):
aux.append("%s")
statement = statement + separator.join(aux) + ")"
print (statement)
return statement
Passing the values, the function produces:
>>>INSERT INTO Publicaciones (item_id,title,price) VALUES (%s,%s,%s)
Which works but, is it there a more pythonic way?
python python-3.x sql
$endgroup$
I'm learning SQL using python library pymysql
.
I wanted to make an INSERT statement that will update 3, 5 or 10 columns using the same code by just passing the Database name, the Columns name and the Values.
Thus I made this:
def insert_statement(db,cols,values):
separator = ","
separator.join(cols)
statement = "INSERT INTO " + db + " (" + separator.join(cols) + ") VALUES ("
aux =
for i in range(0,len(values)):
aux.append("%s")
statement = statement + separator.join(aux) + ")"
print (statement)
return statement
Passing the values, the function produces:
>>>INSERT INTO Publicaciones (item_id,title,price) VALUES (%s,%s,%s)
Which works but, is it there a more pythonic way?
python python-3.x sql
python python-3.x sql
edited Jan 16 at 12:52
Ludisposed
7,80721960
7,80721960
asked Jan 16 at 12:45
Nahuel Varela BlancoNahuel Varela Blanco
1134
1134
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
$begingroup$
You’re not using values
except for its length. But this should be the same length than cols
, so use that instead.
You also don't need a for-loop to build a list with the same element N
times, list multiplication can handle that just fine.
Lastly, I would use f-strings or at least str.format
instead of string concatenation, it is prettyier.
def insert_statement(db, columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', ['item_id', 'title', 'price'])
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
Depending on your calling site, you can also make columns
a variable length argument, it may be easier to use:
def insert_statement(db, *columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', 'item_id', 'title', 'price')
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
But you should limit yourself to only use this function using trusted input. If anything comming from a user enters here, this is a vulnerability waiting to happen:
>>> user_input = "price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :"
>>> insert_statement('Publicationes', 'item_id', 'title', user_input)
"INSERT INTO Publicationes (item_id, title, price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :) VALUES (%s, %s, %s)"
$endgroup$
$begingroup$
Yes! This was exactly what I was looking for. Thank you for all the information. And yes, this function will only be run by my server code without interaction with an user.
$endgroup$
– Nahuel Varela Blanco
Jan 16 at 16:45
add a comment |
$begingroup$
SQL Injections
Even though you are having these proper placeholders for values, your code is still vulnerable to SQL Injection attacks as table and column names are not properly sanitized.
As table and column names cannot be parameterized the usual way, you could validate them separately. For instance, you could check that the table and column names are simply valid MySQL identifiers:
- Python MySQL parameter queries for dynamic table names
Object Relational Mappers
As I understand the purpose of the task is to learn how to interact with the MySQL database via Python MySQL database driver, but, in general, this kind of problems are already solved by different abstraction layer libraries or commonly called ORMs (Object Relational Mappers) like SQLAlchemy, Peewee or PonyORM) which provide an extra Python layer around Python-to-database communication allowing you to basically write SQL queries in Python.
$endgroup$
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
return StackExchange.using("mathjaxEditing", function ()
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix)
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
);
);
, "mathjax-editing");
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: "196"
;
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fcodereview.stackexchange.com%2fquestions%2f211617%2fadd-variable-columns-to-insert-statement%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
$begingroup$
You’re not using values
except for its length. But this should be the same length than cols
, so use that instead.
You also don't need a for-loop to build a list with the same element N
times, list multiplication can handle that just fine.
Lastly, I would use f-strings or at least str.format
instead of string concatenation, it is prettyier.
def insert_statement(db, columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', ['item_id', 'title', 'price'])
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
Depending on your calling site, you can also make columns
a variable length argument, it may be easier to use:
def insert_statement(db, *columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', 'item_id', 'title', 'price')
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
But you should limit yourself to only use this function using trusted input. If anything comming from a user enters here, this is a vulnerability waiting to happen:
>>> user_input = "price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :"
>>> insert_statement('Publicationes', 'item_id', 'title', user_input)
"INSERT INTO Publicationes (item_id, title, price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :) VALUES (%s, %s, %s)"
$endgroup$
$begingroup$
Yes! This was exactly what I was looking for. Thank you for all the information. And yes, this function will only be run by my server code without interaction with an user.
$endgroup$
– Nahuel Varela Blanco
Jan 16 at 16:45
add a comment |
$begingroup$
You’re not using values
except for its length. But this should be the same length than cols
, so use that instead.
You also don't need a for-loop to build a list with the same element N
times, list multiplication can handle that just fine.
Lastly, I would use f-strings or at least str.format
instead of string concatenation, it is prettyier.
def insert_statement(db, columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', ['item_id', 'title', 'price'])
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
Depending on your calling site, you can also make columns
a variable length argument, it may be easier to use:
def insert_statement(db, *columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', 'item_id', 'title', 'price')
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
But you should limit yourself to only use this function using trusted input. If anything comming from a user enters here, this is a vulnerability waiting to happen:
>>> user_input = "price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :"
>>> insert_statement('Publicationes', 'item_id', 'title', user_input)
"INSERT INTO Publicationes (item_id, title, price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :) VALUES (%s, %s, %s)"
$endgroup$
$begingroup$
Yes! This was exactly what I was looking for. Thank you for all the information. And yes, this function will only be run by my server code without interaction with an user.
$endgroup$
– Nahuel Varela Blanco
Jan 16 at 16:45
add a comment |
$begingroup$
You’re not using values
except for its length. But this should be the same length than cols
, so use that instead.
You also don't need a for-loop to build a list with the same element N
times, list multiplication can handle that just fine.
Lastly, I would use f-strings or at least str.format
instead of string concatenation, it is prettyier.
def insert_statement(db, columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', ['item_id', 'title', 'price'])
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
Depending on your calling site, you can also make columns
a variable length argument, it may be easier to use:
def insert_statement(db, *columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', 'item_id', 'title', 'price')
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
But you should limit yourself to only use this function using trusted input. If anything comming from a user enters here, this is a vulnerability waiting to happen:
>>> user_input = "price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :"
>>> insert_statement('Publicationes', 'item_id', 'title', user_input)
"INSERT INTO Publicationes (item_id, title, price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :) VALUES (%s, %s, %s)"
$endgroup$
You’re not using values
except for its length. But this should be the same length than cols
, so use that instead.
You also don't need a for-loop to build a list with the same element N
times, list multiplication can handle that just fine.
Lastly, I would use f-strings or at least str.format
instead of string concatenation, it is prettyier.
def insert_statement(db, columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', ['item_id', 'title', 'price'])
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
Depending on your calling site, you can also make columns
a variable length argument, it may be easier to use:
def insert_statement(db, *columns):
column_names = ', '.join(columns)
placeholders = ', '.join(['%s'] * len(columns))
return f'INSERT INTO db (column_names) VALUES (placeholders)'
Usage:
>>> insert_statement('Publicationes', 'item_id', 'title', 'price')
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'
But you should limit yourself to only use this function using trusted input. If anything comming from a user enters here, this is a vulnerability waiting to happen:
>>> user_input = "price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :"
>>> insert_statement('Publicationes', 'item_id', 'title', user_input)
"INSERT INTO Publicationes (item_id, title, price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :) VALUES (%s, %s, %s)"
edited Jan 16 at 16:42
answered Jan 16 at 13:35
Mathias EttingerMathias Ettinger
24.3k33184
24.3k33184
$begingroup$
Yes! This was exactly what I was looking for. Thank you for all the information. And yes, this function will only be run by my server code without interaction with an user.
$endgroup$
– Nahuel Varela Blanco
Jan 16 at 16:45
add a comment |
$begingroup$
Yes! This was exactly what I was looking for. Thank you for all the information. And yes, this function will only be run by my server code without interaction with an user.
$endgroup$
– Nahuel Varela Blanco
Jan 16 at 16:45
$begingroup$
Yes! This was exactly what I was looking for. Thank you for all the information. And yes, this function will only be run by my server code without interaction with an user.
$endgroup$
– Nahuel Varela Blanco
Jan 16 at 16:45
$begingroup$
Yes! This was exactly what I was looking for. Thank you for all the information. And yes, this function will only be run by my server code without interaction with an user.
$endgroup$
– Nahuel Varela Blanco
Jan 16 at 16:45
add a comment |
$begingroup$
SQL Injections
Even though you are having these proper placeholders for values, your code is still vulnerable to SQL Injection attacks as table and column names are not properly sanitized.
As table and column names cannot be parameterized the usual way, you could validate them separately. For instance, you could check that the table and column names are simply valid MySQL identifiers:
- Python MySQL parameter queries for dynamic table names
Object Relational Mappers
As I understand the purpose of the task is to learn how to interact with the MySQL database via Python MySQL database driver, but, in general, this kind of problems are already solved by different abstraction layer libraries or commonly called ORMs (Object Relational Mappers) like SQLAlchemy, Peewee or PonyORM) which provide an extra Python layer around Python-to-database communication allowing you to basically write SQL queries in Python.
$endgroup$
add a comment |
$begingroup$
SQL Injections
Even though you are having these proper placeholders for values, your code is still vulnerable to SQL Injection attacks as table and column names are not properly sanitized.
As table and column names cannot be parameterized the usual way, you could validate them separately. For instance, you could check that the table and column names are simply valid MySQL identifiers:
- Python MySQL parameter queries for dynamic table names
Object Relational Mappers
As I understand the purpose of the task is to learn how to interact with the MySQL database via Python MySQL database driver, but, in general, this kind of problems are already solved by different abstraction layer libraries or commonly called ORMs (Object Relational Mappers) like SQLAlchemy, Peewee or PonyORM) which provide an extra Python layer around Python-to-database communication allowing you to basically write SQL queries in Python.
$endgroup$
add a comment |
$begingroup$
SQL Injections
Even though you are having these proper placeholders for values, your code is still vulnerable to SQL Injection attacks as table and column names are not properly sanitized.
As table and column names cannot be parameterized the usual way, you could validate them separately. For instance, you could check that the table and column names are simply valid MySQL identifiers:
- Python MySQL parameter queries for dynamic table names
Object Relational Mappers
As I understand the purpose of the task is to learn how to interact with the MySQL database via Python MySQL database driver, but, in general, this kind of problems are already solved by different abstraction layer libraries or commonly called ORMs (Object Relational Mappers) like SQLAlchemy, Peewee or PonyORM) which provide an extra Python layer around Python-to-database communication allowing you to basically write SQL queries in Python.
$endgroup$
SQL Injections
Even though you are having these proper placeholders for values, your code is still vulnerable to SQL Injection attacks as table and column names are not properly sanitized.
As table and column names cannot be parameterized the usual way, you could validate them separately. For instance, you could check that the table and column names are simply valid MySQL identifiers:
- Python MySQL parameter queries for dynamic table names
Object Relational Mappers
As I understand the purpose of the task is to learn how to interact with the MySQL database via Python MySQL database driver, but, in general, this kind of problems are already solved by different abstraction layer libraries or commonly called ORMs (Object Relational Mappers) like SQLAlchemy, Peewee or PonyORM) which provide an extra Python layer around Python-to-database communication allowing you to basically write SQL queries in Python.
edited Jan 16 at 13:44
answered Jan 16 at 13:32
alecxealecxe
15.2k53579
15.2k53579
add a comment |
add a comment |
Thanks for contributing an answer to Code Review Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fcodereview.stackexchange.com%2fquestions%2f211617%2fadd-variable-columns-to-insert-statement%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