MySQL - Literals

MySQL - Literals

What are Literals?

In SQL, a literal is a fixed, constant value. It represents itself, like the number 123 or the string 'Hello, World!'. Literals are used in SQL statements to provide explicit values for comparison, insertion, or assignment.

While literals are fundamental, it’s crucial to understand that in application code, you should almost always use prepared statements with placeholders instead of embedding literals directly into your query strings. This is the single most important defense against SQL injection attacks.

String Literals

A string literal is a sequence of characters enclosed in single quotes (') or double quotes ("). Single quotes are the standard and more portable.

SELECT 'This is a string literal.';
SELECT "This also works, but single quotes are preferred.";

Escaping Special Characters

To include a quote character within a string, you must escape it. You can do this by doubling the quote character or using a backslash (\).

SELECT 'It''s a beautiful day.'; -- Double the single quote
SELECT 'You can also use a backslash \' like this.';

Character Sets

You can specify the character set for a string literal using the introducer syntax (_charset):

SELECT _utf8mb4'Hello, world!';

Numeric Literals

Numeric literals represent numbers. They can be integers, decimals, or in scientific notation.

• Integer: 100, -50
• Decimal/Floating-Point: 123.45, -0.01
• Scientific Notation: 1.23E4 (which is 1.23 * 10^4 = 12300)

SELECT 42;             -- An integer
SELECT 98.6;           -- A decimal value
SELECT 1.5E-3;         -- Scientific notation (0.0015)

Date and Time Literals

Date and time values are typically represented as strings in specific formats. MySQL is flexible but the standard formats are preferred.

• DATE: 'YYYY-MM-DD', e.g., '2023-10-27'
• TIME: 'HH:MM:SS', e.g., '14:30:00'
• DATETIME or TIMESTAMP: 'YYYY-MM-DD HH:MM:SS', e.g., '2023-10-27 14:30:00'

You can also use keywords for temporal literals:

SELECT DATE '2023-10-27';
SELECT TIME '14:30:00';
SELECT TIMESTAMP '2023-10-27 14:30:00';

It’s often better to use functions like NOW(), CURDATE(), and CURTIME() to get current values.

Boolean Literals

MySQL does not have a native BOOLEAN type. It uses TINYINT(1) instead. The keywords TRUE and FALSE are simply aliases for the integer literals 1 and 0, respectively. They are not case-sensitive.

SELECT TRUE, FALSE; -- Returns 1, 0
SELECT true, false; -- Also returns 1, 0

NULL Literal

The NULL literal represents a missing or unknown value. It is not the same as zero (0) or an empty string (''). NULL is case-insensitive.

SELECT NULL; -- Represents an unknown value
SELECT NULL, null, NuLl; -- All are the same

Any arithmetic operation with NULL results in NULL (e.g., 5 + NULL is NULL). Comparisons with NULL require the IS NULL or IS NOT NULL operators.

Hexadecimal and Bit-Value Literals

These are used to specify values byte by byte, which is useful for binary data.

• Hexadecimal: X'hex_string' or 0x... syntax. Example: X'4A6F686E' represents the ASCII string ‘John’.
• Bit-Value: b'binary_string' or 0b... syntax. Example: b'1011' represents the number 11.

SELECT X'4D7953514C'; -- Returns the string 'MySQL'
SELECT 0b1101;         -- Returns the integer 13

Using Literals vs. Prepared Statements

While you will use literals directly when writing queries in a SQL client, it’s critical to avoid them in application code where user input is involved. Concatenating user input into a SQL string is how SQL injection happens.

Unsafe (Don’t do this!)

// PHP Example
$user_id = $_GET['id']; // e.g., '105'
$sql = "SELECT * FROM users WHERE id = $user_id;"; // VERY DANGEROUS!

If a malicious user provides an ID like 105; DROP TABLE users;, your query becomes disastrous.

Safe (Use Prepared Statements)

With prepared statements, the SQL query structure and the data values are sent to the database server separately. The database engine knows that the placeholder value is data, not executable code.

// PHP Example with PDO
$user_id = $_GET['id']; // e.g., '105'

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$user_id]);
$user = $stmt->fetch();

In this safe version, even if a malicious string is provided, it will be treated as a literal string to search for, not as a command to execute.