Skip to main content

Character encoding — how characters map to bytes

You entered a name containing "𩸽" in a registration form; it was saved truncated. Two users with the same name registered, slipping past the unique constraint. Some words don't hit in search.

None of these are explainable while thinking of characters as "things countable one by one". Character-to-byte mapping isn't 1:1, and there are multiple ways to count and compare. This chapter covers that mapping.

What you'll learn in this chapter​

  • Character set and encoding scheme are different things
  • Four ways of counting characters: byte, code unit, code point, grapheme
  • Same-looking characters becoming different bytes, and how to align them
  • Sort order and comparison are decided by collation, not character code
  • Storage limits use bytes, not character counts
  • Multiple interpretation boundaries produce inputs that slip past one side
Prerequisites

No specific prerequisite. The last section deepens "escaping gaps and character encoding traps" from Common web attacks and defenses; reading that chapter ties the discussion together.

What this chapter does not cover​

TopicSee
SQL injection defense itselfCommon web attacks and defenses
Column length design decisionsDatabase Design Guide — The Importance of Database Design
Index effectiveness conditions and key length designIndexes / Database Design Guide — Introduction to index design
Datetime representation shiftsDatetimes and time zones

Character set and encoding scheme are separate​

Split two easily confused things.

DecidesExample
Character setWhich character gets which number (code point)Unicode
Encoding schemeHow to represent that number as bytesUTF-8, UTF-16

"Stored in Unicode" alone doesn't decide bytes. Same "あ" is 3 bytes in UTF-8, 2 bytes in UTF-16. What goes on files or networks are bytes, so mismatched encoding schemes between reader and writer produce mojibake.

WHATWG's Encoding Standard clearly defines what to pick:

New protocols and formats, as well as existing formats deployed in new contexts, must use the UTF-8 encoding exclusively.

— WHATWG Encoding Standard

The standard also notes other encoding schemes remain only for reading existing assets, and using UTF-8 alone eliminates the problems here. New places have no reason to pick anything else.

Four ways of counting​

When you say "character count", there are actually at least 4 different ways to count.

WayWhat's countedWhere it matters
ByteLength after encodingStorage limits, transfer volume, index key length
Code unitEncoding scheme's smallest unit. JavaScript's .length is thisString slicing, slice positions
Code pointNumber of numbers assigned by the character setRegex, per-character processing
Grapheme clusterWhat people recognize as "one character"Display width, cursor movement, character-remaining display
const utf8 = (s: string) => new TextEncoder().encode(s).length;
const graphemes = (s: string) =>
  [...new Intl.Segmenter('en', {granularity: 'grapheme'}).segment(s)].length;

for (const s of ['abc', '日本語', '𩸽', '👨‍👩‍👧']) {
  console.log(
    `${s} → UTF-8 ${utf8(s)} bytes / .length ${s.length} / code points ${[...s].length} / graphemes ${graphemes(s)}`,
  );
}

// Slicing by .length cuts inside a character
const fish = '𩸽の刺身';
console.log('Sliced first "character":  ', JSON.stringify(fish.slice(0, 1)));
console.log('Sliced by code point:      ', JSON.stringify([...fish].slice(0, 1).join('')));
abc → UTF-8 3 bytes / .length 3 / code points 3 / graphemes 3
日本語 → UTF-8 9 bytes / .length 3 / code points 3 / graphemes 3
𩸽 → UTF-8 4 bytes / .length 2 / code points 1 / graphemes 1
👨‍👩‍👧 → UTF-8 18 bytes / .length 8 / code points 5 / graphemes 1

With only ASCII all four match, so testing with alphanumeric alone doesn't reveal differences.

𩸽's .length being 2 is because JavaScript strings are sequences of UTF-16 code units. Characters not fitting in 2 UTF-16 bytes are represented as 2 code units (a surrogate pair). Cutting midway leaves a value that isn't a character alone.

Sliced first "character":   "\ud867"
Sliced by code point:       "𩸽"

The family emoji is even more extreme, joining 5 code points with invisible joiners into 1 appearance. Judging "input up to 10 chars" by .length reaches the limit with 2 emojis. To match the human counting unit, count graphemes.

Same-looking, different bytes​

"が" (ga) has 2 ways to represent. One code point, or "か" plus dakuten (voicing mark) as two.

Unicode aligns this state via normalization.

FormWhat
NFCDecompose, then compose. Shorter combined form
NFDDecompose. Split into base and combining
NFKC / NFKDWith compatibility decomposition. Treats visually different characters like full-width and half-width as same
const composed = 'が';                   // 1 code point
const decomposed = 'が'.normalize('NFD'); // "か" + dakuten, 2 code points

console.log('Appearance      ', composed, decomposed);
console.log('length          ', composed.length, decomposed.length);
console.log('=== comparison  ', composed === decomposed);
console.log('NFC align + ==  ', composed === decomposed.normalize('NFC'));
Appearance       が が
length           1 2
=== comparison   false
NFC align + ==   true

Same appearance, different when compared. This is the truth of "same name registered twice" and "search doesn't hit".

Handling is one: normalize at the entrance and handle only the aligned form afterward. Storage-time and search-time in different forms neither enforce unique constraints nor let indexes work.

Which form to align to depends on use. NFC as default for storage and comparison works well, and NFKC fits search indexes that want to treat "株式会社" (kabushiki-gaisha, "corporation") and "㍿" as the same — NFKC decomposes the latter into the former. NFKC drops information, so don't use where the original representation should be preserved.

Where the decomposed form comes from

The input path can produce different forms from the same keyboard operation. macOS filenames tend to be decomposed — a well-known example. Not reproducing locally isn't proof that form doesn't arrive. Normalizing at the entrance guards against its arrival (Unicode Standard Annex #15 — Unicode Normalization Forms).

Collation decides sort and comparison​

Japanese kana order and "alphabetical order" alike are not character-code numerical order. Sort and comparison rules are decided separately by collation.

const words = ['Zebra', 'apple', 'Ähre'];
console.log('Code unit order    ', [...words].sort());
console.log('German collation   ', [...words].sort(new Intl.Collator('de').compare));
console.log('Case-insensitive collation', new Intl.Collator('en', {sensitivity: 'accent'}).compare('ADMIN', 'admin'));
Code unit order     [ 'Zebra', 'apple', 'Ähre' ]
German collation    [ 'Ähre', 'apple', 'Zebra' ]
Case-insensitive collation 0

In code unit order, all ASCII uppercase (A-Z) come before ASCII lowercase, so Zebra leads, and Ä outside ASCII falls to the end. Not what humans expect.

Row 3 is dangerous in practice. compare returning 0 means "equal", and this collation treats ADMIN and admin as the same string. Database collations follow the same idea, causing:

  • Unique constraints on case-insensitive collation columns judge admin and ADMIN as duplicates
  • Conversely, case-sensitive collation lets both register

Which is right depends on requirements. Email and login names are safer case-insensitive; display names can often be case-sensitive. What matters is deciding — not defaulting without thought — the failure mode.

Collation also affects sort and index. Indexes are ordered structures (Indexes), so changing collation rebuilds the index order.

Storage limits use bytes​

Column definitions are written in character counts, but actual effective limits include byte-based ones.

MySQL example. utf8mb3 uses up to 3 bytes and can't store supplementary characters.

The utf8mb3 character set is deprecated. ... The recommended character set for MySQL is utf8mb4. All new applications should use utf8mb4.

— MySQL 8.4 Reference Manual — The utf8mb3 Character Set

Putting emoji or 𩸽 in a utf8mb3 column errors or truncates depending on settings. The typical cause of "name saved truncated" is this.

Index key length also has byte limits. This value changes with row format.

The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

— MySQL 8.4 Reference Manual — InnoDB Limits

utf8mb4 is up to 4 bytes per character, so in old row formats, columns over 191 characters can't be indexed. "Trying to index VARCHAR(255) gets rejected" comes from here. Limits and default row formats change by version; verify with your environment's documentation.

Multiple boundaries, one slips past​

Common web attacks and defenses positioned escaping as "second-best. Has escaping gaps and character encoding traps". The trap's substance is here.

There are 2 places interpreting byte sequences as characters, and if the 2 follow different rules, inputs that slip past only one side can be created. The position where escaping read "an independent 1 byte" is read on the database side as "part of one character" — that mismatch. The character inserted for escaping gets absorbed into that character, and the quote it was meant to guard stays as it is.

MySQL's client API explicitly notes this dependency:

The mysql argument must be a valid, open connection because character escaping depends on the character set in use by the server.

— MySQL 8.4 C API Developer Guide — mysql_real_escape_string()

Further: changing the connection charset with SET NAMES doesn't change the character set the escape function uses. That 2 being misaligned is exactly the "multiple boundaries" state.

So chapter 17's conclusion becomes first choice. Placeholders remove this path itself, because values don't get mixed as byte sequences into syntax interpretation. For escape, confirm the connection charset and the escape processing charset are the same, per environment.

Same shape of mismatch appears in other layers. Validating filename before normalization and storing after normalization lets a path slip past validation. Validate and execute on the same form — the shared principle.

Common misconceptions​

"Unicode prevents mojibake." — Deciding character set doesn't decide bytes. Reader and writer with different encoding schemes still mojibake. What to decide is encoding scheme like UTF-8.

"1 char = 1 byte, or 1 char = 2 bytes." — UTF-8 is variable from 1 to 4 bytes. Most Japanese is 3 bytes; supplementary characters are 4.

".length gives character count." — Gives UTF-16 code unit count. Surrogate-pair characters are 2, joined emoji even more. To match human counting, count graphemes.

"Same-looking strings are equal by ===." — Different normalization forms are different values. Not aligning at entrance breaks unique constraints and search.

"Sort order is decided by character code numbers." — Decided by collation. In the code unit order .sort() uses, Zebra comes before apple (ASCII uppercase before lowercase), but Ähre falls after both. Case-insensitive collation treats admin and ADMIN as the same.

Exercises​

Q1. "Same display name can't register" is the spec, but the listing shows 2 with the same name. Database has unique constraint, no error. What do you suspect?

Answer and explanation

Suspect the stored byte sequences differ.

Unique constraints compare byte sequences under the collation's rules, so bytes that the collation does not treat as the same value don't count as duplicates. Two candidates:

  1. Different normalization form. One stored composed, other decomposed — different values, both pass. Input path varies (web vs. mobile app registration, etc.)
  2. Similar-looking but different characters. Full-width vs. half-width, or similar glyphs from different character sets

Verify by outputting length and code points of the two rows. Same display but different length or code points → case 1; same length with different numbers → case 2.

Fix by normalizing at the entrance and storing that. Existing data needs migration to the same form. Changing collation alone isn't enough — whether normalization differences are absorbed depends on the collation's implementation, and the stored value itself doesn't change.

Q2. "Profile up to 100 characters" implemented as text.length <= 100. Users report both "shorter but doesn't fit" and "passed the input but truncated on save". Why both?

Answer and explanation

Because .length counts something other than the human-counted "character".

.length is UTF-16 code unit count. Two mismatches arise simultaneously:

  • "Shorter but doesn't fit" — emoji and supplementary characters count 2+ per character. Family emoji is 8, so around 13 hits 100
  • "Passed the input but truncated on save" — the storage side limits in bytes. Japanese is 3 UTF-8 bytes per character, so .length of 100 becomes 300 bytes and exceeds the column limit

The judgment and storage running on different units is the root cause. Align:

  1. Show limits to users in graphemes. Matches "one character" recognition
  2. Verify storage-side limits in bytes. Estimate worst-case bytes from column charset and definition

Either alone is insufficient. Passing the display limit but getting truncated at storage remains as a combination.

Q3. Search using SET NAMES to switch connection charset with values processed by escape function. "Should switch to placeholders" — how to explain?

Answer and explanation

Because the premise for escape working is broken.

Escape needs to know the charset used by the connection to judge "which byte is a quote". But SET NAMES only changes server-side charset, and the character set the escape function uses doesn't change. The 2 misaligning creates a situation where escape reads "independent 1 byte" and the database reads "part of one character". Quotes sneak into the value and get interpreted as syntax.

Placeholders remove this path itself. Values pass through a different lane from syntax, so byte sequence interpretation mismatch doesn't become syntax (Common web attacks and defenses).

To continue with escape, set the connection charset from the API side and confirm escape processing sees the same charset. Adds verification points without reaching the safety of placeholders.

Summary​

  • Character set assigns numbers; encoding scheme makes bytes. In new places, use only UTF-8
  • Character counting has 4 ways: byte, code unit, code point, grapheme. Alphanumeric matches, so tests don't reveal differences
  • JavaScript's .length is code unit count. Slicing there cuts inside a character
  • Same appearance, different bytes exist. Normalize at the entrance and handle only aligned forms
  • Sort and comparison are decided by collation. Case-insensitive treats admin and ADMIN as the same, changing unique constraint meaning
  • Storage limits use bytes. utf8mb4 is up to 4 bytes per character; index key length limits change by row format
  • Two places interpret bytes as characters, mismatch rules and one slips past. Placeholders remove this path
Related references

This concludes Part 14.