CODEWITHSUNDEEP
pythonmysql
ZodiacV1
ZodiacV1

Reputation: 11

Cannot insert string into a MySQL table

I am trying to insert a string into a SQL table, but the string value is blank after the insert. What am I doing wrong and what should I do to fix it?

This is the python function I am running.

def newCharacter(usr):
    cur.execute("INSERT INTO stats (user, level, exp, hp, attack, luck, aggro, hpregen) VALUES ('{}', 1, 0, 100, 10, 50, 50, 0)".format(usr))

Below is an example of my stats table after inserting two rows using the above newCharacter function. The first column (which is blank below) should contain a string (e.g. "Username").

     |     1 |   0 | 100 |     10 |   50 |    50 |       0 |
     |     1 |   0 | 100 |     10 |   50 |    50 |       0 |

Upvotes: 1

Views: 460

Answers (2)

KeatsKelleher
KeatsKelleher

Reputation: 10191

Generally speaking it's important to sanitize your input to the database when it comes from a user. If users can input whatever they like you may find control characters and syntactically meaningful input. This is even if you have client-side validation.

The MySQL drivers you're using implement this pretty easily:

cur.execute("INSERT INTO stats (user, level, exp, hp, attack, luck, aggro, hpregen) VALUES (%s, 1, 0, 100, 10, 50, 50, 0)", usr)

This will allow you to "escape" problematic characters. It will also ensure the value interpolated there is properly quoted, which, I imagine is your problem.

Upvotes: 0

Schwern
Schwern

Reputation: 164809

I'm not sure what's wrong, but what you've written is vulnerable to a SQL injection attack. Anything of the form "make a SQL query by concatenating variables together" (and .format counts as concatenating) is vulnerable to a SQL injection attack.

Instead, pass the variables in as parameters to .execute.

cur.execute("INSERT INTO stats (user, level, exp, hp, attack, luck, aggro, hpregen) VALUES (?, 1, 0, 100, 10, 50, 50, 0)", usr)

There's several different types of parameter styles, it might not be ?.

Upvotes: 1

Related Questions