-2
        #generate password hash and insert the user into the database
        hash = generate_password_hash(password)
        db.execute("INSERT INTO users(username, hash,email) VALUES (?,?,?)",username,hash,email)

I wanted to insert into the database but

it keeps giving me the error and I have also created users table in my SQL database and it has all the three columns in it. what can be the solution because i'm not seeing any syntax error in my code

davidism
  • 121,510
  • 29
  • 395
  • 339
Abdirashid Abubakar
  • 1
  • Please post the full traceback. Make sure you have balanced parentheses on the line before this code block. – Barmar Dec 16 '22 at 18:53
  • What error does it give? – easleyfixed Dec 16 '22 at 18:54
  • A [mre] needs to be complete enough to cause the same error when run without changes. (It might be easier to build one with SQLite than mysql where it isn't really a database-specific problem). Also, include the _full_ exception in the body of the question; the summary in the title isn't enough to work with. – Charles Duffy Dec 16 '22 at 19:02

1 Answers1

1

The parameters to db.execute() must be in a single argument.

db.execute("INSERT INTO users(username, hash,email) VALUES (?,?,?)",(username,hash,email))

But this shouldn't cause the syntax error you describe.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • @easleyfixed Maybe your organization is different, but we don't create stored procedures for every insert/delete we do in the database. We only use stored procedures if there's complex logic to implement and it makes more sense to do it on the DB server than the client code. – Barmar Dec 16 '22 at 19:06
  • I have been using MySQL for over 20 years, and I have encountered a situation where using a stored procedure was better only once. They are de rigueur in some other brands of RDBMS, but in MySQL stored routines are virtually never a good idea. – Bill Karwin Dec 16 '22 at 19:09
  • I've yet to find a case where it wasn't better other than not having to spend the extra 30 seconds typing it up. I guess if you don't have the actual equipment near you and must rely on another company to do that for you (AWS etc) that would make sense. But why would you NOT want the additional validation and protection done? Its pretty easy to Inject into your MySQL if you just put it inline like that. – easleyfixed Dec 16 '22 at 19:31
  • You mentioned stored procedures above, but now you seem to be talking about parameterized queries. These are two very different features. – Bill Karwin Dec 16 '22 at 19:37
  • Now you are talking semantics. A stored procedure could be just a simple SELECT * FROM TABLE, and you could technically do that inline, but it would run faster directly on the server instead of parsing it in the front end, passing it and waiting on the return. If there aren't many records it wont matter but if you start getting a few thousand rows/cols, a sp would pull quicker, or at least my tests seem to indicate that. But a SP can have IN/OUT on it and STILL be called a stored procedure. Workbench shows tables/views/functions/stored procedures as the things it can contain. – easleyfixed Dec 16 '22 at 19:54
  • @easleyfixed I've never seen a stored procedure that does anything complex that's readable, due to the limited control structures and data structures that are availble. Even the above one-line `INSERT` query would be 5-10 lines in a stored proc, due to all the boilerplate needed to declare the proc then prepare and execute the statement. – Barmar Dec 16 '22 at 20:05
  • Our system that consists of a large C application and hundreds of PHP scripts has about 5 stored procedures and functions, and about 5 views. – Barmar Dec 16 '22 at 20:07
  • 1
    Anyway, this isn't the place to debate the merits of SP, if this goes on longer the mods will shut it down. – Barmar Dec 16 '22 at 20:07
  • Fair enough, true sorry to have turned this into a sp debate hehe. I have the opposite going on where we luckily have MS servers, so we can use ASP instead of php. If a user can type something into the statement, I validate frontend manually, then the sp can be the 2nd catch all to make sure they nothing silly gets inserted or deleted. But yeah I will end it here, thanks for the convo, and good luck to OP on his issue! – easleyfixed Dec 16 '22 at 20:19