Quotes

A common error in SQL is with quotes. Luckily, compared to other databases, MariaDB and MySQL are very forgiving.

For example, suppose I have a “products” table for an online store. If I want to look up every product which has a price of 5.99 or more I might use the following query:

MariaDB [crashcourse]> select prod_id, prod_name, prod_price from products
    -> where prod_price >= 5.99;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| ANV01   | .5 ton anvil   |       5.99 |
| ANV02   | 1 ton anvil    |       9.99 |
| ANV03   | 2 ton anvil    |      14.99 |
| OL1     | Oil can        |       8.99 |
| TNT2    | TNT (5 sticks) |      10.00 |
| FB      | Bird seed      |      10.00 |
| SAFE    | Safe           |      50.00 |
| DTNTR   | Detonator      |      13.00 |
| JP1000  | JetPack 1000   |      35.00 |
| JP2000  | JetPack 2000   |      55.00 |
+---------+----------------+------------+
10 rows in set (0.02 sec)

MariaDB and MySQL are also perfectly happy if the “where” line is changed to

where prod_price >= '5.99';

or even (if sql_mode is not “ANSI_QUOTES”):

where prod_price >= "5.99";

All of the above work because the value being looked up is an integer. If instead I want to see all of the anvils I’m selling I might use a query such as:

MariaDB [crashcourse]> select prod_id, prod_name, prod_price from products
    -> where prod_name like '%anvil%';
+---------+--------------+------------+
| prod_id | prod_name    | prod_price |
+---------+--------------+------------+
| ANV01   | .5 ton anvil |       5.99 |
| ANV02   | 1 ton anvil  |       9.99 |
| ANV03   | 2 ton anvil  |      14.99 |
+---------+--------------+------------+
3 rows in set (0.02 sec)

However, if I execute the query without any quotes or if I use double quotes (“) around the search string when sql_mode = “ANSI_QUOTES” I will see an error:

MariaDB [crashcourse]> select prod_id, prod_name, prod_price from products
    -> where prod_name like %anvil%;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%anvil%' at line 2

Basically, without the quotes, or when sql_mode = “ANSI_QUOTES”, the server does not know how to interpret the ‘%anvil%’ string.

To be safe, the rules I always try to follow are:

  • use single quotes (‘) around strings
  • do not use quotes around integers
  • avoid the use of double quotes (“)

BTW: The examples above use the sample data from MariaDB Crash Course by Ben Forta.