Welcome!
This is the community forum for my apps Pythonista and Editorial.
For individual support questions, you can also send an email. If you have a very short question or just want to say hello — I'm @olemoritz on Twitter.
MySQL Client
-
@Tizzy The triple quotes are not important. That's a Python syntax feature which allows you to write a string literal across multiple lines. SQL doesn't care about newlines, so it makes no difference whether you put everything in one line or on multiple lines.
The important part is the question mark in the query. For example, you should write
cursor.execute("select name from ?", [tablename])
instead of
cursor.execute("select name from %s" % [tablename])
The difference is that the second variant uses standard Python string formatting (i. e. the value of
tablename
is just put into the string at the position of the%s
), which leaves you vulnerable to injection attacks. Iftablename
was taken from a public web form, then you could entermytable; drop table mytable
as the table name, which would result in a query ofselect * from mytable; drop table mytable
and delete your data.In the first variant, we don't use the standard Python formatting. Instead we put a question mark in the query string and pass the table name as the second argument to
cursor.execute
, which internally escapes the string properly to avoid any code injection attacks. (We have to puttablename
in square brackets to make a single-element list - if we had five question marks, we'd pass a five-element list with all the values to insert.)Though I think this question-mark insertion only works in some cases. Now that I think about it, it might only be allowed for
where
clauses and such. Perhaps because letting users specify arbitrary table names is dangerous enough? Not sure... -
@Tizzy sorry for not explaining myself better. @dgelessus is correct about the
?
being the important part.I checked MySQL and it has its own syntax for variable arguments that just so happens to be
%s
. Shame on them… -
thank you @blmacbeth and @dgelessus both. Even though I'm not using these for webforms, I want them to be as secure as possible. However, I'm failing to get either of your syntax examples to work where
tableName
is a variable of type string. Tried on both Python 2.7 and 3.5.cursor.execute("select * from ?", (tableName,))
TypeError: not all arguments converted during string formatting
cursor.execute("select * from ?", [tableName])
TypeError: not all arguments converted during string formatting
cursor.execute("select * from ?", tableName)
Traceback (most recent call last): File "/Users/anty/Documents/devAndGraphic/xCode7era Dev/FiveStarsV2/Script/databaseExamples.py", line 153, in <module> print( getSequelData("someTable") ) File "/Users/anty/Documents/devAndGraphic/xCode7era Dev/FiveStarsV2/Script/databaseExamples.py", line 91, in getSequelData cursor.execute("select * from ?", [tableName]) File "/Library/Python/2.7/site-packages/pymysql/cursors.py", line 156, in execute query = self.mogrify(query, args) File "/Library/Python/2.7/site-packages/pymysql/cursors.py", line 135, in mogrify query = query % self._escape_args(args, conn) TypeError: not all arguments converted during string formatting
-
@Tizzy - quoting @blmacbeth:
I checked MySQL and it has its own syntax for variable arguments that just so happens to be
%s
. Shame on them…Even though you should not use normal
%
-formatting, MySQL apparently uses the same format codes. That is, you still have to use%s
as the placeholder. I've only used thesqlite3
module from the standard library, and there the placeholder is?
. -
@dgelessus So if we can't use the
?
syntax, then is usingcursor.execute("statement %s",tableName)
more secure thancursor.execute("statement"+tableName)
? -
What exactly the placeholder is doesn't matter, it could be
?
or%s
or something else depending on the module. The important part is that you do not simply insert arguments into the query string using standard python features like"select " + arg
,"select %s" % arg
or"select {}".format(arg)
. All of those are BAD.What you need to do is pass the query string, including placeholders, as the first argument to
cursor.execute
(or whatever function), and as the second argument a list/tuple of arguments. When you do this, the DB module gets the unformatted query string and the arguments that you want to insert. Then it can do whatever escaping and security checks necessary to ensure that no code injection is possible.A safe query might look like
cursor.execute("select %s", (arg,))
. Note that even though we have a%s
in the query, we do not format it using Python's%
operator. We pass the query string and the arguments to theexecute
function, which then does the formatting for us in a safe way. -
@dgelessus thanks for the education, I really do appreciate it. I tried to tackle this before at some point unsuccessfully and went back to doing it like I always had since there's no part in my use-case where a user manually creates input of their choosing (other than authentication to a 3rd party ... so the third party would have to want to hack me..). But let's figure this out once and for all...said I.
No matter how hard I try, using all of the syntaxes suggested here, can I pass a table name through as a parameter of the execute method.
I was only able to use the suggested syntax for passing a value ...such as below.
Works:
cursor.execute("SELECT * FROM someTable WHERE ID = (%s)",("1",))
Doesn't Work:
cursor.execute("SELECT * FROM (%s) WHERE ID = '1' ",("someTable",))
-
Are the parenthesis around the table name valid here?
You might also try using
escape_string
on the database connection object. -
@JonB I get the same result with or without the parentheses, both in the success and failure examples. So it looks like the parentheses aren't necessary, but they work (with the success example above)
success:
cursor.execute("SELECT * FROM someTable WHERE ID = %s", "1" )
Failure:
cursor.execute("SELECT * FROM %s WHERE ID = '1' ","someTable")
I couldn't find anything called "escape_string" in the python 2/3 docs.
-
has some good discussion. Seems you cannot substitute table names. A few options are to write your own escaping method, use sqlalchemy (included in pythonista), or use property mapping.
-
Right,
sqlite3
does that too, you aren't allowed to insert table names. Their logic is probably "if you take table names from an untrusted source, you're already insecure". -
Thanks @dgelessus @JonB @Tizzy
This has been very helpful for an SQL noob like me! -
So I have produced a game which has a highscore table for each of three difficulty settings. I have now adapted this to use a MySQL database to store results for any user, rather than just a local highscore table.
For this I set up a limited database user account (not my admin account!) with privileges restricted to INSERT and SELECT (i.e. not DROP, TRUNCATE etc). Is it safe/acceptable to bundle the account username and password in the code so people can use this feature? Would you suggest a different way?
I have not pushed it to GitHub yet as I'm new to SQL and wanted some advice! :)
(I am aware someone could cheat and simply use the details to insert a top score if they wished, but I'm hoping for honesty!)Thanks
-
This post is deleted! -
I got the connector from Tomasso Turchi. It seems to connect ok but when I try to execute "show tables;" it gives me an error: "no localization support for language 'eng'". This seems to come from a line in file locales.py . Is there any fix for this?
Thank you!
-
-
Using tommasoturchi's mysqldb connector, I'm doing this:
import mysqldb db = mysqldb.connect(host="my.host.com", user="user", passwd="pass", db="MyDBName", port=3306) cursor = db.cursor()
and I'm getting an error:
ord() expected string of length 1, but int found
all the way down in thenetwork.py
file atself._packet_number = ord(packet[3])
wassup with that?!
Thanks
-
@iAmMortos said:
Using tommasoturchi's mysqldb connector, I'm doing this:
import mysqldb db = mysqldb.connect(host="my.host.com", user="user", passwd="pass", db="MyDBName", port=3306) cursor = db.cursor()
and I'm getting an error:
ord() expected string of length 1, but int found
all the way down in thenetwork.py
file atself._packet_number = ord(packet[3])
wassup with that?!
Thanks
I think the port should be a string.
-
My Recommendation is to use MySQL connector Python
Why?
- it works with both Python 2 and 3.
- Because it is official Oracle driver for MySQL for working with Python.
- It is purely written in Python
- You can also use C extension to connect to MySQL.
- MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python.
Follow these article to work with MySQL Connector Python
Install MySQL Connector Python
Connect to MySQL from Python -
I concur with @rahuldev2910 - I also recommend MySQL Connector/Python (disclaimer: I work for Oracle and write about MySQL Connector/Python).
Latest version is 8.0.12 and also include the new X DevAPI (the mysqlx) module if you want NoSQL access to MySQL.
I wrote a blog on https://mysql.wisborg.dk/2018/08/31/mysql-connector-python-on-ios-using-pythonista-3/ how to install MySQL Connector/Python in Pythenista 3.