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 - 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.