TypeError: coercing to Unicode: need string or buffer, NoneType found

Question

I am trying to create a SQL string from a python script.

this is the code:

sql_query = ('insert into ithelpdesk ' 
        '(id,display_id,subject,description,priority,status,requester_name,source,responder_id,due_by,updated_at,frDueBy,ticket_type,created_at)'
        ' values ('
        '"' + str(data['id']) + '",'
        '"' + str(data['display_id']) + '",'
        '"' + data['subject'] + '",'
        '"' + data['description'] + '",'
        '"' + str(data['priority']) + '",'
        '"' + str(data['status']) + '",'
        '"' + data['requester_name'] + '",'
        '"' + str(data['source']) + '",'
        '"' + str(data['responder_id']) + '",'
        '"' + str(data['due_by']) + '",'
        '"' + str(data['updated_at']) + '",'
        '"' + str(data['frDueBy']) + '",'
        '"' + data['ticket_type'] + '",'
        '"' + str(data['created_at']) + '"'
        ')')

when I run I am getting this traceback message:

Traceback (most recent call last):
 File "c:\users\swatson\dropbox\source files\winpython\dash\newTickets.py", line 198, in <module>
main()
 File "c:\users\swatson\dropbox\source files\winpython\dash\newTickets.py", line 159, in main
addNew(n)
File "c:\users\swatson\dropbox\source files\winpython\dash\newTickets.py", line 65, in addNew
'"' + str(data['created_at']) + '"'
TypeError: coercing to Unicode: need string or buffer, NoneType found

Now I know for a fact (by doing a printstr(data['created_at'])) that there is a value at data['created_at'], and I also know that is is the same value as with data['updated_at'] so I am at a loss as to how to resolve this?

Martijn Pieters Martijn Pieters · Accepted Answer · 2014-07-01T09:43:49

Your fundamental mistake here is to try and build a query as one string. Don't do that. That way SQL injection vulnerabilities lie.

Use SQL parameters instead:

sql_query = ('insert into ithelpdesk ' 
        '(id,display_id,subject,description,priority,status,requester_name,source,responder_id,due_by,updated_at,frDueBy,ticket_type,created_at)'
        ' values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
    (data['id'], data['display_id'], data['subject'], data['description'],
     data['priority'], data['status'], data['requester_name'], data['source'], 
     data['responder_id'], data['due_by'], data['updated_at'], data['frDueBy'],
     data['ticket_type'], data['created_at']))

The exact syntax depends on your database adapter; it could be you need to use %s. Many database adapters also support named parameters, in which case you don't even need to pass in 15 separate values. You just name the keys to extract from the dictionary, in the query:

sql_query = ('insert into ithelpdesk ' 
        '(id,display_id,subject,description,priority,status,requester_name,source,responder_id,due_by,updated_at,frDueBy,ticket_type,created_at)'
        ' values (:id, :display_id, :subject, :description, '
        '         :priority, :status, :requester_name, :source '
        '         :responder_id, :due_by, :updated_at, :frDueBy, '
        '         :ticket_type, :created_at)',
    data)

The Python DB API standard supports a few different parameter styles. For positional parameters ? or %s can be used, for named, :name or %(name)s. sqlite3 uses the first styles, MySQLDB the latter.

The exception is thrown you are concatenating unicode data with string data, and there is a None in there somewhere. Perhaps data['ticket_type'] is None.

TypeError: coercing to Unicode: need string or buffer, NoneType found

1 Answers