Mysql should use apostrophe in mysql queries?

Question

What is the correct way to write a query in a MySQL database for numeric data types:

SELECT * FROM accounts WHERE id = 5; 

or

  SELECT * FROM accounts WHERE id = '5'; 

Basically I prefer the latter using ' because it is more consistent with text data types.

Does it affect performance?

Answer 1

Quoting for strings, MySQL will read these quotes and then discard them by an integer, this is slower than just passing it int.

Honestly, the difference in performance is not significant, but it's exactly the same as writing a program that stores numbers in strings and then throws it into int when it needs to do some math. This is bad practice.

Answer 2

I doubt that you can measure any noticeable difference between the speed of two queries. If you care about efficiency, you have to make sure that you have an index in the id column. If you do, both queries will be very fast.

However, there are security concerns.

MySQL official opinion

MySQL client security guidelines recommend using quotation marks.

A common mistake is to protect only string data values. Remember to also check the numerical data. If the application generates a query, such as SELECT * FROM table WHERE ID = 234, when the user enters 234, the user can enter 234 OR 1=1 to force the application to generate a query SELECT * FROM table WHERE ID=234 OR 1=1 . As a result, the server retrieves each row in the table. This expands every line and causes an excessive load on the server. The easiest way to protect against this type of attack is to use single quotes around numeric constants: SELECT * FROM table WHERE ID='234' .

Emphasis is mine.

My opinion

Although quotation marks are recommended in the documentation, it is neither necessary nor sufficient to prevent the described attack. For example, changing the attacker's line to 234' OR '1'='1 will lead to a victory over their approach.

In my opinion, the best way to make your application safe is to use parameterized queries instead of entering user values ​​directly into the string.

If for some reason you cannot use parameterized queries, then do not use quotation marks, but make sure that the variable does contain an integer using intval .

Answer 3

Depending on the type of identifier, you can use either "5" or 5. Usually id is the primary key and is of type int, so you should use 5

Answer 4

Use quotation marks when the field type in your database is a string. Otherwise, if it is numeric, do not use quotation marks. If you use quotation marks for the type of the number field, this can really slow down the queries, because mysql must match the strings for the numbers.

Answer 5

Using a quoted number for a column whose type is defined as a numeric one has a worse performance value compared to using an unquoted value, since the server must convert the string to the desired type during query compilation. In addition, this has no effect, and it will probably be difficult for you to click on the target. (Note that both "0" and "0" are sent to the server as strings, they must be converted to the internal type of the field before using it anyway, since sending "0" simply forces an extra step. First the parser parses "0", then the optimizer notices that the column type is numeric and converts it accordingly. OTOH, with 0, the parser will parse it as a numeric value [save as long int iirc], and then notice the field type and convert the numeric value for match field type, if necessary. Thus, the difference is really insignificant).

However, using an unquoted number for a column whose type is defined as text is a very bad idea, as this means that the server cannot use any index in the column to resolve the query.

It is important to understand that, although any line that numbers exactly one numeric value has an almost infinite number of lines that are numbered for a given numeric value. Consider "0", "0E0", "0.0", etc. This explains why quoting a constraint when a field is numeric is not very bad, there is only an operation to perform, and it explains why NOT quoting a constraint is bad when the field is not numeric, as this means that the server must make each line in table before the number before performing the comparison, thereby forcing a table scan.