In MySQL, should I quote numbers or not?

Question

For example - I create database and a table from cli and insert some data:

CREATE DATABASE testdb CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
USE testdb;
CREATE TABLE test (id INT, str VARCHAR(100)) TYPE=innodb CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
INSERT INTO test VALUES (9, 'some string');

Now I can do this and these examples do work (so - quotes don't affect anything it seems):

SELECT * FROM test WHERE id = '9';
INSERT INTO test VALUES ('11', 'some string');

So - in these examples I've selected a row by a string that actually stored as INT in mysql and then I inserted a string in a column that is INT.

I don't quite get why this works the way it works here. Why is string allowed to be inserted in an INT column?

Can I insert all MySQL data types as strings?

Is this behavior standard across different RDBMS?

Nidanidaros

Answer 1

MySQL is a lot like PHP, and will auto-convert data types as best it can. Since you're working with an int field (left-hand side), it'll try to transparently convert the right-hand-side of the argument into an int as well, so '9' just becomes 9.

Strictly speaking, the quotes are unnecessary, and force MySQL to do a typecasting/conversion, so it wastes a bit of CPU time. In practice, unless you're running a Google-sized operation, such conversion overhead is going to be microscopically small.

Sardius

I think the quotes can also lead to a situation where an index is not used because of the casting. So I too recommend to do it properly and never use quotes on numbers. That will also make a possible transition to a more standard compliant DBMS easier. – Abstractionism

I guess my primary beef was: when I do inserts from PHP - all numbers are quoted by default. It looks like I MUST use PDO::PARAM_INT for number to be unquoted. I simply don't get why so many examples online omit these really required PDO::PARAM_ constants. – Nidanidaros

PDO, for all that it's the generally recommended solution, is a total pile of crap when it comes to bound parameters and type-conversion. – Sardius

@marc B: what do u recommend? mysqli? mmm. I don't recall any other alternative. Mysql C api defines code for each mysql type: dev.mysql.com/doc/refman/5.5/en/…, but either mysqli or PDO only give you 4-5 type bindings. mysqli: (i,f,s,d) and PDO: (PDO::PARAM_INT, PDO::PARAM_STR etc.) – Nidanidaros

Any of the mysql libraries work fine, it's just that PDO lets people be lazy and not have to deal with escaping things themselves. On the other hand, there's a fair number of query-types that CAN'T be done with prepared statements and you'll have to go "bare metal" instead./ – Sardius

So I suppose without quotes would be faster? – Teratism

What if I have a varchar column with only numbers as registries. Maybe you ask why and the answer is because i have some registries beginning with a zero as '0203'. So, what is better for me? A simple query like SELECT * FROM my_col WHERE my_registry = 203, or SELECT * FROM my_col WHERE my_registry = '0203'? – Congress

Answer 2

You should never put quotes around numbers. There is a valid reason for this.

The real issue comes down to type casting. When you put numbers inside quotes, it is treated as a string and MySQL must convert it to a number before it can execute the query. While this may take a small amount of time, the real problems start to occur when MySQL doesn't do a good job of converting your string. For example, MySQL will convert basic strings like '123' to the integer 123, but will convert some larger numbers, like '18015376320243459', to floating point. Since floating point can be rounded, your queries may return inconsistent results. Learn more about type casting here. Depending on your server hardware and software, these results will vary. MySQL explains this.

If you are worried about SQL injections, always check the value first and use PHP to strip out any non numbers. You can use preg_replace for this: preg_replace("/[^0-9]/", "", $string)

In addition, if you write your SQL queries with quotes they will not work on databases like PostgreSQL or Oracle.

Uniocular

Then why does mysql put quotes around numbers we right click -> Copy row in Workbench?? There's like 5 different copy row options, but not one of them can tell if a column is a number or not. – Androgen

Answer 3

Check this, you can understand better ...

mysql> EXPLAIN SELECT COUNT(1) FROM test_no WHERE varchar_num=0000194701461220130201115347;
+----+-------------+------------------------+-------+-------------------+-------------------+---------+------+---------+--------------------------+
| id | select_type | table                  | type  | possible_keys     | key                  | key_len | ref  | rows    | Extra                    |
+----+-------------+------------------------+-------+-------------------+-------------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | test_no | index | Uniq_idx_varchar_num | Uniq_idx_varchar_num | 63      | NULL | 3126240 | Using where; Using index |
+----+-------------+------------------------+-------+-------------------+-------------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM test_no WHERE varchar_num='0000194701461220130201115347';
+----+-------------+------------------------+-------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table                  | type  | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------------------+-------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test_no | const | Uniq_idx_varchar_num | Uniq_idx_varchar_num | 63      | const |    1 | Using index |
+----+-------------+------------------------+-------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> SELECT COUNT(1) FROM test_no WHERE varchar_num=0000194701461220130201115347;
+----------+
| COUNT(1) |
+----------+
|        1 |
+----------+
1 row in set, 1 warning (7.94 sec)

mysql> SELECT COUNT(1) FROM test_no WHERE varchar_num='0000194701461220130201115347';
+----------+
| COUNT(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Emissive

With some more explanation this could be a very instructive answer. – Date

It looks ilke it's just wayyy slower if you don't provide quotes – Unmistakable

@Unmistakable my guess is because the quoted query was ran second and could take advantage of the first query being cached – Lyra

@apricity, No, that's not it. For one thing, the second query would result in a separate cache entry. But even if you disable the query cache, it will be much faster, because it will do an efficient index lookup instead of an index scan. Look at the rows field reported by the EXPLAINs. – Urga

Answer 4

This is not standard behavior.

For MySQL 5.5. this is the default SQL Mode

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

ANSI and TRADITIONAL are used more rigorously by Oracle and PostgreSQL. The SQL Modes MySQL permits must be set IF AND ONLY IF you want to make the SQL more ANSI-compliant. Otherwise, you don't have to touch a thing. I've never done so.

Cruiser

I'm curious - how does this answer the OP's question? – Presnell

umm. yeah. i'm not changing mode to ansi or traditional. I have default mysql setup. – Nidanidaros

The last part says : Is this behavior standard across different RDBMS? Let me clarify: It is not standard behavior for different RDBMS's. – Cruiser

Answer 5

AFAIK it is standard, but it is considered bad practice because
- using it in a WHERE clause will prevent the optimizer from using indices (explain plan should show that)
- the database has to do additional work to convert the string to a number
- if you're using this for floating-point numbers ('9.4'), you'll run into trouble if client and server use different language settings (9.4 vs 9,4)

In short: don't do it (but YMMV)

Presnell

Where did you get the information that indices are not used? My EXPLAIN result looks exactly the same with or without quotes – Catalogue

According to the MySQL 5.6 documentation, indices are not used when comparing string columns to integer values: "For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:" - see dev.mysql.com/doc/refman/5.6/en/type-conversion.html . Although the OP's scenario is the other way around, I'd still not rely on the RDBMS to use indices in that case (AFAIK, at least one other major RDBMS (Oracle) doesn't). – Presnell

It works fine with MySQL, and properly uses the index. Personally, I wrap everything in single quotes when working with MySQL to standardize things, and it just means I don't use BOOL values in MySQL. I guess when I first did it I was nervous someone could more easily escape out of a value if it wasn't wrapped with single-quotes, but that's poppy-cock. – Eradis

Answer 6

It depends on the column type! if you run

SELECT * FROM `users` WHERE `username` = 0;

in mysql/maria-db you will get all the records where username IS NOT NULL.

Always quote values if the column is of type string (char, varchar,...) otherwise you'll get unexpected results!

Carlow

Answer 7

You don't need to quote the numbers but it is always a good habit if you do as it is consistent.

Intarsia

why is this consistent? could you please explain that? – Ulla

1 OR 1=1 or something similar that can cause unnecessary load on the server or sql injection if unquoted. It becomes a second nature to quote everything in a sql query however if you are picky and then a sql query that should have been quoted as number is not, then sql injection may occur (check online for this and how many victims there are to it). Although quoting numbers does cause a little load on the server but even if you performing around 100K queries every second still you would only save a total of 11 seconds for 100K queries which comes down to less that a millisecond for each query. – Intarsia

It converts the datatype itself. – Intarsia

quoting does not help against sql injection – Ulla

it does...it breaks the sql query. We are still assuming that you are going to escape the data for the numbers aswell. – Intarsia

well, escaping (or casting) data helps. quoting does not. – Ulla

@Ulla Anush's argument regarding security would seem to be perfectly valid. From the MySQL manual page on Security Guidelines - "A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. ... – Engender

...cont... This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it." – Engender

@w3d that is exactly what wonk0 said. If you don't escape it, you can always inject. But making a string out of it doesn't help a bit. To take your example: If the user enters 234' OR 1=1 -- , you still have an injection. SELECT * FROM table WHERE ID='234' OR 1=1 -- ' TL;DR: Just sanitize your input. (And use prepared statements where avaliable) – Parasitology

@domenukk: Well, the quoting primarily helps in the first example. If the data is only escaped (which Anush had assumed but only states in his last comment) then the attack is still present and quoting the value makes this harmless: '234 OR 1=1'. Your example would become '234\' OR 1=1' (which is still valid), but without the quotes it would break. But I agree, this is a poor mans protection. Like you say, sanitizing/parameterized queries are preferred. – Engender

@w3d #139699 – Parasitology

@w3d how about this input: 234\' OR 1=1 -- ? It becomes '234 \\' OR 1=1 -- => INJECTION. As you said, poor mans protection. Do it proper. :) – Parasitology

Answer 8

The issue is, let's say that we have a table called users, which has a column called current_balance of type FLOAT, if you run this query:

UPDATE `users` SET `current_balance`='231608.09' WHERE `user_id`=9;

The current_balance field will be updated to 231608, because MySQL made a rounding, similarly if you try this query:

UPDATE `users` SET `current_balance`='231608.55' WHERE `user_id`=9;

The current_balance field will be updated to 231609

Marchall

No, the current_balance field will NOT be updated to 231608 nor 231609, it will be updated to 231608.09375 and 231608.546875 respectively regardless of whether you use quotes or not. Find out yourself by SELECT user_id, FORMAT(current_balance,24) FROM users – Catalogue

Don't ever use float for absolute precision such as account balances. Use the decimal column type. – Lepine