Understanding XSS and SQL injection through safe learning examples

XSS and SQL injection occur at different boundaries, but they share a fundamental question: does the application treat data as data, or accidentally give it the meaning of instructions? In HTML output, the browser determines which characters become structure. In a database query, SQL processing determines which parts belong to the statement. Appropriate protection therefore belongs at the respective interpretation boundary.

This guide uses a fully isolated learning example with PHP and an in-memory SQLite database. It accesses no production server or existing customer records. Three fictional project titles are created and searched through a parameterized query. The result is then emitted as HTML text. This small path makes both boundaries testable without pretending to supply a finished user management system or a general security approval.

1. Distinguish the two interpretation boundaries

SQL injection concerns the construction and interpretation of a database statement. If a variable title becomes part of SQL through string concatenation, data and query syntax can become mixed. Parameterized statements maintain the intended separation for values. The application controls the actual query shape. Deciding which records a person is entitled to see remains a separate business responsibility.

XSS concerns another boundary. An application can retrieve a value correctly and subsequently insert it into a browser document inappropriately. The database lookup is then properly bound while the output remains problematic. Ordinary text between HTML elements needs suitable output encoding. Database parameters do not replace this treatment. Conversely, encoding HTML does not protect a SQL statement assembled by concatenation.

Draw the actual path of one piece of information: input, business validation, storage, query and output. Mark each place where another language or interpretation begins. The learning example contains two such transitions. A real application may have others, including URLs, JavaScript, CSS and email templates. The handling follows the specific context. A reassuringly named function such as cleanEverything is more likely to obscure these differences than to resolve them.

2. Keep the learning environment bounded

The sample records are Entwurf O'Reilly, A & B and the literal string <em>Probe</em>. They deliberately contain characters that can have meaning in different languages. An apostrophe belongs in an ordinary name. An ampersand belongs in ordinary titles. The HTML-like string is a harmless structural probe. Learning the distinction requires no attack against somebody else's application and no access to private records.

The database exists only for the script's lifetime. Every execution recreates the same small collection. Searching for Entwurf O'Reilly should return exactly that title. Searching for an unknown title should return no records. The lookup uses an exact comparison rather than fuzzy search syntax. This keeps expectations unambiguous and makes a failed result easier to investigate. The example is intentionally small enough to understand from beginning to end.

PHP needs PDO and the SQLite driver for this script. The actual checks use PHP 8.4.27. SQLite is chosen for isolated reproducibility, not to imply that a production MySQL connection behaves identically in every configuration. Adapting the pattern requires separate decisions about connection options, data models, account privileges and business authorization in the real environment. Reproducing a learning result does not settle those deployment questions.

3. Bind values while controlling query structure

The lookup contains a placeholder for the requested title. The program determines the SQL text. The value is supplied separately when the statement executes. The PHP documentation for PDO::prepare explains placeholders and their limits. They represent data values. They are not a general mechanism for substituting table identifiers, column names or an entire sorting instruction.

If an interface offers sorting, the application therefore needs a bounded mapping. An allowed selection can correspond to a column explicitly defined in code. An unknown selection is rejected or receives a defined default. The application does not accept an arbitrary column name from a request. This decision belongs to controlled query structure and needs review independently of binding the remaining values.

A call to prepare is also insufficient if variable data has already been copied into its SQL argument. Inspect the actual construction rather than searching only for a reassuring method name. The OWASP SQL Injection Prevention Cheat Sheet identifies parameterized queries as a primary measure. In this example, the relevant detail is that inserting the sample values and searching for a title both use that separation. The fixed schema definition contains no variable input.

4. Follow the complete PHP path

Save the following script as a PHP file and execute it through the command line. Its first argument selects the title to search for. Without an argument it uses Entwurf O'Reilly. The output is a small HTML document containing result titles as ordinary list text. The helper is deliberately named htmlText because it serves this particular HTML text context. The fictional data retain their German spelling in both language versions.

<?php
declare(strict_types=1);
function htmlText(string $value): string {
    return htmlspecialchars($value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}
$db = new PDO('sqlite::memory:');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec('CREATE TABLE projects (id INTEGER PRIMARY KEY, title TEXT NOT NULL)');
$insert = $db->prepare('INSERT INTO projects (title) VALUES (:title)');
foreach (["Entwurf O'Reilly", 'A & B', '<em>Probe</em>'] as $title) {
    $insert->execute(['title' => $title]);
}
$name = $argv[1] ?? "Entwurf O'Reilly";
$query = $db->prepare('SELECT id, title FROM projects WHERE title = :title');
$query->execute(['title' => $name]);
$rows = $query->fetchAll(PDO::FETCH_ASSOC);
echo '<!doctype html><html lang="en"><meta charset="utf-8">';
echo '<title>Isolated learning example</title><ul id="results">';
foreach ($rows as $row) {
    echo '<li>' . htmlText($row['title']) . '</li>';
}
echo '</ul></html>';

The fixed CREATE TABLE statement contains only the structure chosen by the script. Variable titles are subsequently bound. Selection and rendering remain separate steps. If a check fails, you can distinguish an incorrect record selection from an incorrectly interpreted display of a correct record. This distinction is more useful for diagnosis than a blanket message declaring an input unsafe. It points to the boundary that actually needs a correction.

5. Encode HTML text at the output boundary

The PHP reference for htmlspecialchars explains conversion of particular HTML-significant characters. The example specifies encoding and options explicitly. The em-like string should remain visible rather than creating an additional em element. Apostrophes and ampersands should also preserve their intended readable information. The objective is correct interpretation of ordinary content, not removal of every character that might have special meaning somewhere.

Do not automatically store the version encoded for one HTML output as your general data model. The same title may later be needed in a text export or another interface. Fixing one presentation into storage complicates reuse and can lead to double encoding. Preserve the business-validated value and transform it for the actual output. This is an architectural decision with an identifiable owner, rather than a filter added wherever a string happens to pass.

The function is not called safeString because its result is not universally appropriate. A value inserted into JavaScript, CSS or a URL requires separate consideration of that context. Even for HTML attributes, the particular attribute matters. Encoding quotation marks does not by itself turn an arbitrary URL into an allowed destination. The OWASP XSS Prevention Cheat Sheet explains why these contexts must be distinguished. Keep the helper's stated scope narrower than the application's overall security requirements.

6. Treat JavaScript text rendering as its own case

When an application updates an ordinary title in the DOM, a text interface is an appropriate starting point. The MDN documentation for textContent describes the property. An assignment such as element.textContent = title treats the value as text. For a project title, that is different from deliberately processing an HTML fragment. Select the operation that matches the information's intended meaning.

Make this distinction visible in a component interface. A parameter called title is a text value. Deliberately permitted editorial HTML needs another treatment and a clearly defined trust model. Combining both under one ambiguous content parameter makes later misuse easier. A type or an explicitly named interface can support the intended separation. The point is not the naming convention alone, but the decision it communicates to the next person changing the component.

Switching one assignment to text output does not inspect every DOM path. A component can set its main heading correctly while rendering a supplementary note differently. Identify the actual sinks. This learning example checks its visible list items. A real interactive project list would additionally need its other elements and state changes assessed. A successfully checked text field is not evidence that the entire interface uses appropriate handling everywhere.

7. Separate business validation from technical interpretation

Safe output handling does not decide which project names a business wants to permit. Length, required fields and business rules are checked where they belong. An empty title may be unacceptable even though its encoded output creates no HTML structure. A very long value can affect layout or processing. Give those questions explicit rules rather than blending them into output encoding or treating them as proof that every downstream use is safe.

Conversely, an apostrophe is not a sensible general reason to reject a name. The example deliberately uses one as part of an ordinary fictional title. Banning selected words or characters cannot reliably replace the interpretation-boundary decision. Validation should describe the real data requirement. The database query remains parameterized even for values that satisfy all business rules. Passing validation is not permission to concatenate a title into SQL.

Write errors that help someone correct the input. A message about maximum title length describes a business limit. It need not expose database internals. Developers retain a record of the corresponding rule. The team can then see whether a test examines an input requirement or the technical treatment of a value. Both can be necessary, but they answer different questions and should have separate expected outcomes.

8. Bound authorization and record selection separately

The script intentionally contains no login or customer assignment. It only handles its own fictional in-memory collection. Production project management needs a decision about who may read or change each record. A correctly parameterized lookup does not answer that authorization question. It can reliably execute a business query that is too broad and still return information the current person should not receive.

Plan access as an independent requirement. A project identifier supplied in a request is not proof of permission. The application must establish the allowed relationship between user and record. Which additional condition belongs in the query depends on the actual model. This article does not invent a universal multi-tenant management system. Copying one query pattern must not hide the fact that authorization remains a separate unresolved design task.

The database account's technical privileges should also fit the job. A read operation does not automatically require unrestricted schema changes. Review the actual configuration in the database system used. The memory example deliberately creates schema and data inside one process. That simplification makes it convenient for learning; it is not a proposal for allocating production server permissions. Record the differences when preparing an adaptation so the example's convenience is not mistaken for operational guidance.

9. Check ordinary and unusual values

Check these expectations against actual output. Comparing the PHP source alone is insufficient. Execute the script for each lookup and load the resulting document in a browser. The list must contain the expected number of entries. For a match, the text must be exactly the expected title and must contain no unintended child elements. This joins the query observation to the browser interpretation rather than treating them as unrelated assurances.

Empty inputs and near-matching strings are useful additional cases. The example does not claim to implement similarity search. A difference should therefore not suddenly produce additional records. These checks document this concrete function. They do not establish that every future extension is already correct. If wildcard search, sorting or additional filters are introduced, extend the expectations to cover the new behavior and its interpretation boundaries.

10. Make the check recognize a known mistake

A check is more informative when it can recognize the mistake it is meant to catch. An isolated faulty variant therefore omits HTML text encoding for the harmless em probe. The browser creates an em child element. The structural check must reject that variant. The faulty version is not supplied as a usable production pattern and touches no third-party data. It serves a narrow purpose: showing that the test distinguishes text from parsed structure.

This countercheck examines output. On the query side, several exact lookups establish that only the expected fictional record is returned. An unknown title returns none. Connecting those observations is more useful than declaring that every input has been cleaned. It states what was executed and which boundary was inspected. Reviewers can see the evidence and its limits instead of relying on a broad adjective such as secure.

Record the runtime, driver and test values. When another driver or database is introduced, the team can repeat the relevant cases deliberately. A production database does not need to be copied to investigate the basic separation. Functions with additional business conditions require their own suitable test collection. Preserve that collection with the expectations so a future maintainer can reproduce the intended behavior rather than merely rerun a script that reports success.

11. Design error handling and diagnostics deliberately

The learning script treats database errors as exceptions. A production interface needs a controlled failure path. Visitors should see neither credentials nor raw query details. A general error can end the operation intelligibly while internal diagnostics retain enough context to investigate the actual failure. Those are separate audiences with different information needs. Decide what each receives rather than exposing the development output unchanged.

Logging is not a reason to collect every input indefinitely. Specify which information diagnosis requires and how it is protected. The scope particularly needs justification for personal or confidential content. The isolated tests store only fictional values and technical outcomes. They contain no actual contacts or project records. That bounded collection is sufficient for the learning objective and avoids making a production data export a hidden prerequisite.

Remember that logged values may later be displayed. A correctly bound input does not become trusted HTML because it appears in a log file. Administrative views still require context-appropriate output. This return path is easily missed when a review focuses only on the public input form. Include the later reader of the information in the data-flow map, especially where a different component or template performs the rendering.

12. Use a focused adaptation checklist

• Document each variable value's business meaning and route through the application.
• Keep SQL text separate from bound data values.
• Resolve dynamic identifiers through an explicitly allowed fixed mapping.
• Encode HTML text appropriately at the output boundary.
• Assess other output contexts separately.
• Review login, authorization and record selection independently.
• Do not ban ordinary punctuation as a substitute for correct technical separation.
• Check expected records and actual browser structure with fictional data.
• Make the test detect at least one known faulty variant.
• Include failure paths and later administrative displays.

Use precise feedback during review. Instead of calling an input unsafe, identify that a title is copied into SQL before binding, or that the results view interprets an ordinary text value as HTML. Such findings lead to targeted changes and relevant checks. They discourage adding another filter in the wrong place. They also help a reviewer distinguish an implementation mistake from a business requirement that has not yet been specified.

After a change, repeat the affected path from beginning to end. Correct lookup and correct rendering belong together in this example. If another display is introduced later, it must not inherit an assumed approval from the first one. Dependable security work comes from reviewable decisions at each actual boundary and from maintaining those decisions as the application evolves. A small executable example helps explain that practice while remaining honest about what it does not implement.

13. Do not silently broaden search behavior

Changing an exact comparison to pattern matching is a business change. The SQLite documentation for LIKE explains the percent and underscore pattern characters. A bound parameter remains a data value, but a search operation can deliberately give that value pattern semantics. Binding and deciding whether visitors may supply patterns are therefore different questions. The checked script deliberately implements no LIKE search.

If a project list later needs partial-name search, describe the intended meaning first. Should a fragment return every matching project? Should a percent sign be searched as an ordinary part of a title? What happens when the search field is empty? What result limits are appropriate? These answers determine the cases the new function must check. Existing exact-title tests remain useful because they document the previous contract and expose which expectations have changed.

The same principle applies to rendering extensions. Highlighting a search term in a result requires a deliberate division of the text. It is not a reason to interpret the entire stored title as HTML. Plan highlighting as a separate presentation using controlled elements and text values. The underlying data retain their meaning. Review whether the complete title stays readable and whether additional elements arise only from the intended presentation.

A short change note helps with handover: identify the changed search meaning, the new output locations and the expectations that still apply. Another person can then understand the adaptation without reconstructing the entire application. The note points to concrete checks; it does not replace them. Small extensions particularly need this clarity because they are easily described as merely a visual improvement even when they change interpretation or selection behavior.

Sources and scope

Sources were opened on 9 November 2026. The linked OWASP, PHP and MDN primary material supports the specifically identified foundations. The data, execution path and test expectations are isolated learning material created for this guide. No third-party applications are tested and no comprehensive security approval, certification or real project outcome is asserted.