Cannot insert string into a MySQL table

Question

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 |

Can you please add to your question a specific error message, or the results you received and explain how they differ from what you expected? — eric.christensen
– eric.christensen, Commented Nov 18, 2016 at 21:32
May be default auto-commit is turned off and you need to do cursor.commit() — Moinuddin Quadri
– Moinuddin Quadri, Commented Nov 18, 2016 at 21:35

Schwern · Accepted Answer · 2016-11-18 21:37:01Z

1

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

answered Nov 18, 2016 at 21:37

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ZodiacV1 Over a year ago

Oh thank you, how would you recommend I add a variable to the table then?

Schwern Over a year ago

@ZodiacV1 Just like I wrote.

KeatsKelleher · Accepted Answer · 2016-11-18 21:56:30Z

0

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.

answered Nov 18, 2016 at 21:56

KeatsKelleher

10.3k4 gold badges49 silver badges53 bronze badges

Collectives™ on Stack Overflow

Cannot insert string into a MySQL table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related