Quote problem when doing INSERT SQL

Asked

Viewed 1,344 times

5

I’m doing a CRUD with PDO, but the method query prepare() only works with "crooked quotes"

Ex (that’s how it works): 

"INSERT INTO `_user` (`firstname`) VALUES ('blabla')"

It doesn’t work anymore:

"INSERT INTO '_user' ('firstname') VALUES ('blabla')"

That’s problem with encoding or something?

php sql pdo

  • Add code and error message.

  • No error, query just doesn’t work.

  • You are using Mysql?

  • You must use backtick \`` em nomes de campos ou tabelas, com prepared statements não é necessário usar aspas simples nos valores basta fazer o bindValue()ou passar todos os valores noexecute()`.

  • Yes, Mysql with PDO

  • The funny thing is that I don’t have this problem in select, this is only in Insert?

  • No, the rule is the same in any SQL command.

Show 2 more comments

2 answers

12


Single quotes should only be used in values. So they are ok in VALUES ('blabla'). Already in table names, columns and aliases, or you don’t use anything, or you use backticks `. These backticks are only required if the name of your table, column, or alias matches some reserved Mysql term. So in your second query, this part:

INSERT INTO '_user' ('firstname')

has syntax errors. The simplest correct query would look like this (already in a string):

"INSERT INTO _user (firstname) VALUES ('blabla')"

Example of use that requires backtick, if you had an INT type column called "order" (which is reserved word):

"INSERT INTO _user (firstname, `order`) VALUES ('blabla', 1)"

It doesn’t hurt to remember that it is always good to avoid using reserved words such as database name, table, column, alias, index, function, precedent etc.

  • 1

    Thank you very much, clarified cool!

  • In addition, it is possible to use reserved words. For this, it is necessary to apply the inverted quotation marks (backticks).

4

the backticks, or crase, or "crooked quotes", are part of the default Mysql identifiers (I’m not sure if the sql language itself or the SGDB). You can see more about this on mysql documentation

Browser other questions tagged php sql pdo

You are not signed in. Login or sign up in order to post.