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
Here's my code. It's the connect line that raises the error.import mysqldb db = mysqldb.connect(host= "http://my_host", user = "user", passwd="password", db="my_database", port = 3306) cursor = db.cursor()
-
@chriswilson Running your code strictly as is does bring up the same error. However, as soon as I entered a valid database url, the error goes away. Are you sure you're inputting the correct IP address/url?
-
I think so - I'm using the hostname given by my provider (in the format http://mysql.my_host.co.uk). I just used the name of the database without a path - is this ok?
-
@chriswilson remove the 'http://' part of the url.
So it's just 'mysql.my_host.co.uk'
You don't need a path for the database. Just a name.
-
@Tizzy That seems to have worked - so simple when you know. Thanks!
Now I just need to figure out how to use the mysqldb module! :)
-
@chriswilson Here's a couple examples of how to do a few things with some premade functions for getting a list of tables, checking if a table exists, creating a table, getting the last saved entry, and getting a dump of all the data in a table. Obviously you have to fill out the credentials with your own stuff.
Let me know if you have any questions.
try: import pymysql as mysqldb except ImportError: import mysqldb def dbConnect(): #port 3306 is the default for mysql #returns false if it can't successfully connect username = "username" password = "password" dataBase = "databaseOnServer" porty = 3306 try: conn = mysqldb.connect(host="host.something.com", user=username,passwd=password,db = dataBase,port=porty) except mysqldb.err.OperationalError: print("Can't connect. Check your hostname/user/password info.") conn = False return conn def getLatestSavedEntry(tableName): #gets the latest entry, the one with largest ID (must be a table with "ID set as an auto-incrementing primary key") #createTable function does this. conn=dbConnect() if conn ==False: print("no connection") return cursor = conn.cursor() try: cursor.execute("SELECT * FROM "+str(tableName)+" where ID = (SELECT MAX(ID) FROM "+tableName+")") lastEntryData=cursor.fetchone() if lastEntryData == None: lastEntryData = ["doesnt","exist...nope"] except: lastEntryData =["table","doesn't","exist","...probably"] print(lastEntryData) cursor.close() return lastEntryData def createTable(tableName): conn = dbConnect() if conn ==False: print("no connection") return cursor=conn.cursor() #adjust this string with the sequel commands you'd like, columns etc. sequelString = "CREATE TABLE "+str(tableName)+"(ID INT(11) PRIMARY KEY AUTO_INCREMENT, uuid VARCHAR(50),request_at DOUBLE, duration INT, totalDuration INT, ratingHistoryCalculatedAverage DOUBLE,ratingHistory5 INT, ratingHistory4 INT, ratingHistory3 INT, ratingHistory2 INT, ratingHistory1 INT, Surge VARCHAR(30), fare DOUBLE, fareTakeHome DOUBLE, Distance DOUBLE)" try: print(".....trying table creation") cursor.execute(sequelString) print("created new table!") return "Success" except: print("table couldnt be created...") return "Failure to create" cursor.close() def getSequelData(tableName): #this gets all of the data in your selected database and table, returns False if conn=dbConnect() if conn ==False: print("no connection") return cursor = conn.cursor () #get the vertsion of your mysql cursor.execute("SELECT VERSION()") row = cursor.fetchone() queryString = "SELECT * FROM "+str(tableName) try: cursor.execute(queryString) data=cursor.fetchall() print(data) except mysqldb.err.ProgrammingError: print("DOESN'T EXIST, YOU MUST CREATE THIS TABLE TO BE ABLE TO FETCH ITS CONTENTS.") data = False cursor.close() return data def doesTableExist(tableNameToCheck): tableNameToCheck = str(tableNameToCheck) tableList = getTableList() if tableList ==False: print("no connection") return for table in tableList: #tableList is a list of tuples of unicode w/second of tuple empty existingTable = str(table[0])#gets to unicode string #print(existingTable,"???",tableNameToCheck) if existingTable == tableNameToCheck: print("table "+tableNameToCheck+" already exists. Yay!") userTableExists = True break else: userTableExists = False if userTableExists: #print("Table exists. moving on.") return True elif not userTableExists: #print("Table not found. Maybe you should create it.") return False def getTableList(): conn = dbConnect() if conn ==False: print("no connection") return False cursor = conn.cursor() #cursor.execute("select * from information_schema.tables") cursor.execute("SHOW TABLES") tableList=cursor.fetchall() #print(tableList) cursor.close() return tableList if __name__=="__main__": #tests print( getLatestSavedEntry("someTable") ) print( createTable("someTable") ) print( getSequelData("someTable") ) print( getTableList() ) print( "table exists?:",doesTableExist("someTable") )
-
@Tizzy it is good practice to not do
statement = 'SELECT * FROM ' + table_name'
or evenstatement = 'SELECT * FROM ' + %s' % table_name
as this can cause security issues with SQL injection. Most database packages (read: modules) will have something along the lines ofstatement = ''' SELECT * FROM ? ''' with db.connect as conn: result = conn.execute(statement, (table_name,))
This is a more secure way of accessing batabases. The other way is good enough for person projects, but keep that in mind or little Bobby Tables will make your life awful as a DBA.
B.
-
@blmacbeth are you saying to use triple quotes for security purposes?
-
@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!