13070

When to surround SQL fields with apostrophes?

Question:

I notice that when I INSERT and SELECT values to and from a database I have to surround the fields with single quotes, like so:

mysql_query("INSERT INTO employees (name, age) VALUES ('$name', '$age')");

However, if I were to update the age, I would not use single quotes:

mysql_query("UPDATE employees SET age = age + 1 WHERE name = '$name'");

Also, it seems when adding the date to a SQL database I do not have to surround it with single quotes either:

mysql_query("INSERT INTO employees (name, date) VALUES ('$name', NOW())");

Also, when using operators like CONCAT it seems not to be necessary either:

mysql_query("UPDATE employees SET name=CONCAT(name,$lastName) WHERE id='$id'");

Perhaps I am just coding poorly but I seem to recall if I did not surround a field with single quotes when inserting and selecting it the operation failed.

Answer1:

You need to surround the values with quotes when field data type is of <strong><em>string</em></strong> eg text, char, varchar, etc or <strong><em>date</em></strong> types such as date, time, datetime.

For <strong><em>numerical</em></strong> types such as int, bigint, decimal, etc or SQL functions such as now(), current_date, you don't need quotes.

Answer2:

"age" exists in the question as both a php variable ($age) and as a MySQL column name. Column names shouldn't be quoted (generally speaking) but the contents of a column, used in a select or insert statement, ought to be quoted.

In particular, if the contents of a php variable haven't been set, the variable itself will vanish and this can break your syntax. Surrounding php variables with single quotes will at least protect the syntax in case the variable vanishes.

SELECT * from something where age = $age;

If for some reason $age wasn't set, such as the user didn't enter it on input, it will simply vanish and this line of code will produce a syntax error at run time because it becomes "where age = ;"

SELECT * from something where age = '$age';

If for some reason $age wasn't set, it will disappear but won't generate an error because it will become "where age = '';" and is still good syntax.

SQL injection is still possible in this instance of course but that's a different question.

Answer3:

You have to make a distinction between what kinds of things you see in a query:

<ul><li>reserved sql keywords: SELECT, UPDATE, WHERE, NULL, ... (not case-sensitive, but mostly used uppercase)</li> <li>(sql) operators, and syntax tokens: + - / * . ( ) etc etc</li> <li>sql functions: NOW(), CONCAT(), ...</li> <li>fields, table names, database names: employees, age, name, date, ... which <em>should</em> be quoted using backticks, like `field`, to avoid confusion e.g. if you name a field ORDER</li> <li>values</li> </ul>

The last group, the values, can be string literals like 'John' or "John", or numbers like 1, 10, 1e9, 1.005. NULL is a special value, which you can loosely describe as "not set".

Numbers don't have to be enclosed in quotes, but string literals do.

This description is far from complete or perfect, but it should give you a beginning of understanding.

Answer4:

String values (including single characters) must be enclosed in single quotes. This includes date constants represented using strings. Numeric values do not need quotes.

Recommend

  • phpMyAdmin trigger gui checking age
  • Using hibernate return count value of a query
  • mysql convert date to same date of current year
  • Javascript 2D array sorting - by numerical value
  • Adding scores to MongoDB aggregation using $switch
  • Specifying field size of Map collection in grails DOM
  • Spark DataFrame equivalent to Pandas Dataframe `.iloc()` method?
  • table design + SQL question
  • R: merging copies of the same variable
  • What is #:: method
  • Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure
  • SQL Server - Is there a collation that provides natural order for numbers?
  • How to detect interior vertices in groups of 2d polygons? (E.g. ZIP Codes to determine a territory)
  • Convert Type Decimal to Hex (string) in .NET 3.5
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • Groovy: Unexpected token “:”
  • ActiveRecord query for a count of new users by day
  • How to create a file in java without a extension
  • Illegal mix of collations for operation for date/time comparison
  • Why doesn't :active or :focus work on text links in webkit? (safari & chrome)
  • output of program is not same as passed argument
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Validaiting emails with Net.Mail MailAddress
  • MySQL WHERE-condition in procedure ignored
  • Deserializing XML into class C#
  • How to set my toolbar fixed while scrolling android
  • Statically linking a C++ library to a C# process using CLI or any other way
  • Web-crawler for facebook in python
  • Why winpcap requires both .lib and .dll to run?
  • AT Commands to Send SMS not working in Windows 8.1
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Buffer size for converting unsigned long to string
  • trying to dynamically update Highchart column chart but series undefined
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • apache spark aggregate function using min value
  • Sorting a 2D array using the second column C++
  • java string with new operator and a literal
  • How can I use threading to 'tick' a timer to be accessed by other threads?