PDO prepared queries: separate values, ordering and search patterns

Prepared PDO statements separate SQL structure from supplied data values. That is an important foundation for inspectable database access in PHP. The separation works only when outside content is actually bound as data. A command assembled from unchecked strings does not become trustworthy merely because prepare is called afterwards.

This article develops a small catalogue search with a category, search text, ordering and result limit. The code was executed against an artificial SQLite fixture. Particular attention goes to places where placeholders do not solve the whole problem: column names are structure, percent has its own meaning inside a LIKE pattern and a bound value is not automatically a suitable business input.

Distinguish values from structure explicitly

The requested term and selected category are data. Table names, selected columns and ordering instructions belong to the command structure. That distinction should remain visible in code. Otherwise SQL fragments, quotes and outside values become an opaque mixture whose meaning is difficult to inspect after small extensions.

The PDO prepare documentation describes placeholders for complete data literals rather than arbitrary command sections. Our search pattern can therefore be bound to :term. A requested sort column instead selects from a fixed mapping. A placeholder containing a column name would not perform that structural selection.

The distinction also focuses review. Data values need appropriate business validation and binding. Variable structure needs an examination of the fixed choices the application actually offers. An outside value should neither select an additional table nor append another SQL fragment behind an ordering instruction.

Define the search contract first

The search should find a literal part of a title. Someone entering percent is looking for an actual percent character. CSS_ should not automatically permit any character where the underscore occurs. That is a product decision for this search. Another application can deliberately offer a pattern language, provided it explains that behavior equally clearly.

The artificial catalogue contains 24 entries, including HTML 100%, CSS_Aufbau and JavaScript!. It also contains duplicate titles and equal durations. This allows checks of deterministic ordering as well as matching records. Every entry is fictional teaching material, not a customer offer or an analysis of a real course catalogue.

An empty term is permitted by this contract. With all, it produces a limited initial list rather than an error. That should be a deliberate product decision: another application might require a specific term before searching. Empty text and missing authorization are different matters. This fixture's initial list contains only its artificial public resources and grants no additional access rights.

Treat connection configuration as a prerequisite

The search function receives an already-configured PDO object. The executed experiment created a new in-memory SQLite database and explicitly selected exception mode. It connected to no external server and used no credentials. The prepared read operates on the known resources table. Creating that table belongs to the learning fixture.

$db = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

A production application configures credentials and privileges separately. The executing identity should hold only the privileges needed for its task. The test fixture is created and then read. That is not a recommendation to expose a public search endpoint through an identity allowed to change the schema, nor to include connection credentials in visitor responses.

The complete verified search function

The function accepts an input array with optional known fields. It is not an HTTP endpoint and renders no HTML page. This bounded design makes data access independently testable. An actual web application can add its request adapter before the function and its output presentation afterwards, retaining their separate responsibilities.

<?php
declare(strict_types=1);

function searchResources(PDO $db, array $input): array {
    $defaults = ['q' => '', 'category' => 'all', 'sort' => 'title', 'limit' => '20'];
    if (array_diff(array_keys($input), array_keys($defaults))) {
        throw new InvalidArgumentException('Unknown search field');
    }
    $values = $defaults;
    foreach ($input as $key => $value) {
        if (!is_string($value) || strlen($value) > 512) {
            throw new InvalidArgumentException('Expected bounded scalar string');
        }
        $values[$key] = $value;
    }
    if (!preg_match('/\A.{0,80}\z/us', $values['q'])
        || preg_match('/[\x00-\x1F\x7F]/', $values['q'])) {
        throw new InvalidArgumentException('Invalid search text');
    }
    if (!in_array($values['category'], ['all', 'web', 'code', 'design'], true)) {
        throw new InvalidArgumentException('Unknown category');
    }
    $orders = [
        'title' => 'title COLLATE NOCASE ASC, id ASC',
        'duration' => 'duration_minutes ASC, id ASC',
    ];
    if (!isset($orders[$values['sort']])
        || !preg_match('/\A(?:[1-9]|[1-4][0-9]|50)\z/', $values['limit'])) {
        throw new InvalidArgumentException('Invalid order or limit');
    }
    // These are LIKE-pattern escapes, not manual SQL quoting.
    $pattern = '%' . strtr($values['q'], ['!' => '!!', '%' => '!%', '_' => '!_']) . '%';
    $sql = "SELECT id, title, category, duration_minutes FROM resources WHERE title LIKE :term ESCAPE '!'";
    if ($values['category'] !== 'all') { $sql .= ' AND category = :category'; }
    $sql .= ' ORDER BY ' . $orders[$values['sort']] . ' LIMIT :row_limit';
    $statement = $db->prepare($sql);
    $statement->bindValue(':term', $pattern, PDO::PARAM_STR);
    if ($values['category'] !== 'all') {
        $statement->bindValue(':category', $values['category'], PDO::PARAM_STR);
    }
    $statement->bindValue(':row_limit', (int)$values['limit'], PDO::PARAM_INT);
    $statement->execute();
    return $statement->fetchAll(PDO::FETCH_ASSOC);
}

Before business validation, supplied values must be scalar strings below a small byte cap. Text, category, order and number rules follow. An omitted optional field receives its documented default. A present but invalid field is not silently replaced with a conveniently valid fallback. This makes faulty callers easier to diagnose.

Understand what binding actually provides

The prepared command contains fixed parameter positions. bindValue associates accepted values with those positions. Search text and category use string parameters; the already-validated limit uses an integer parameter. The PDO bindValue documentation describes this handover. Business validation explicitly precedes binding in the example.

A parameter type does not replace that validation. Our function does not accept every string that might become a small integer after conversion. It first accepts only the canonical representation from one to fifty, then converts it. The code consequently preserves an understandable account of which inputs the interface permits.

The experiment also bound a supposed column name as a value in a SELECT statement. Its result was the string title rather than the contents of the title column. This executed counterexample makes the literal-versus-structure distinction concrete. It does not replace database documentation, but it helps prevent a frequent misunderstanding when first using placeholders.

Select ordering from fixed choices

The orders mapping contains only two fixed SQL fragments. title selects title ordering and duration selects duration ordering. Both add ID as a tie-breaker. The outside choice is not written directly into the command. It only serves as the key for the existing mapping. Unknown keys are rejected.

The OWASP SQL-injection prevention guidance explains this combination of bound data and fixed structural choices. Our mapping does not expose every column that happens to exist in the table. It describes the two business options offered. A syntactically valid database column does not automatically become a publicly selectable option.

The tie-breaker matters when titles or durations are equal. Without it, a limited list could vary among equivalent rows. The fixture deliberately includes those ties. Its independently computed reference therefore checks the complete ordered rows, rather than comparing only an unordered set of identifiers or the number of matches.

LIKE has its own pattern language

A safely bound term can still produce an excessively broad result for the intended task. In LIKE, percent represents a sequence of characters and underscore represents one character. That is pattern interpretation inside a data value. It is separate from whether the value can alter the surrounding SQL command.

The function consequently chooses an explicit escape character: exclamation mark. An input percent becomes !% in the pattern, underscore becomes !_ and an entered exclamation becomes !!. Only afterwards does the application add its own leading and trailing percent characters. Those deliberately allow the literal term anywhere in a title.

The SQLite LIKE and ESCAPE documentation defines this pattern behavior. The resulting complete pattern is still bound as one parameter, not inserted into SQL source text. This character treatment is therefore not homemade general SQL quoting. It implements a specific business search rule inside the bound data value.

Verify literal characters using actual matches

Percent matched IDs 1, 23 and 11 in the executed fixture. Underscore matched 3 and 11. Exclamation matched 5, 24 and 11. Their order follows the offered title ordering. They did not match every catalogue row. The test makes visible that these characters are actually treated literally here.

Another artificial resource has an SQL-like title. Searching for x' OR 1=1 -- returned precisely its ID 12. The text was searched as content rather than executed as a condition. The table also retained all 24 rows. Such counterexamples establish behavior of this specific code, without becoming a blanket promise about arbitrary SQL fragments added in the future.

Assess Unicode and collation as product behavior

The term must be valid UTF-8 and contain at most eighty code points. Its byte cap is examined separately first. These limits address different concerns: one expresses the permitted text length and the other bounds unnecessarily large values before further work. Neither determines which upper- and lowercase characters the database equates.

The actual SQLite setup treats ASCII case equivalence in LIKE by default, but does not generally extend that behavior across Unicode. Accordingly, Über matched ID 9 while über matched ID 10. Those two fictional records were not automatically equated. A linguistically appropriate search needs a deliberate collation policy and potentially additional search facilities.

COLLATE NOCASE in the ordering mapping is likewise a SQLite-specific decision. It must not silently be described as general German dictionary ordering. Accents, combining sequences and other languages require closer consideration. Before adopting the function on another system, state the actual product requirement and compare that system's behavior with it.

Add categories and limits deliberately

The all option omits the category condition. A known specific category adds a fixed comparison with its own placeholder. No named parameter is therefore reused multiple times in one template. PDO documentation discusses the distinction around repeated names and emulated preparation. Small, unambiguous templates are easier to review in this area.

The row limit is bound as well. It remains an accepted number rather than a free-form LIMIT fragment. A higher permitted limit would nevertheless be an operational choice. Fifty is not a universal boundary for fast responses. Search duration also depends on data volume, access paths, patterns and actual server load. This experiment measured no elapsed query times.

Distinguish failures from empty results

A valid search without matches returns an empty array. Invalid input instead raises InvalidArgumentException. With the configured PDO mode, a database problem propagates as PDOException. The experiment verified this using a separate database without the expected table. A missing schema was therefore not presented as an empty successful search.

The PDO error-handling documentation describes exception mode. An actual website needs an appropriate outer boundary that chooses understandable visitor feedback and necessary internal diagnostics. A complete connection failure containing paths and credential-related details must not be copied indiscriminately into a public error message.

Keep SQLite and MySQL distinct

PDO offers a common calling interface without making every SQL expression or database characteristic identical. This function was executed with SQLite 3.53.4 and PHP 8.4.27. No MySQL server was started for the experiment. Its ordering collation and exact search behavior need adaptation and testing against the real driver before migration.

The MySQL 8.4 prepared-statement documentation explains server-side support and distinguishes programming interfaces from SQL PREPARE and EXECUTE commands. That is not an instruction to wrap every PDO call in additional SQL preparation commands. Investigate the actual PHP connection's driver and settings, including which form of preparation it uses.

Avoid unsupported performance promises about reuse

The PHP prepared-statement introduction demonstrates repeated execution with different values. This can be useful for recurring operations. Our search function, however, prepares its command inside each function call. The experiment consequently establishes neither a particular saving from statement reuse nor a specific request speed.

If a real process performs many similar queries, statement lifetime can be organized separately. That design still needs a clear account of which connection and template are reused and when a cursor must be closed. Answer those operational questions from the actual workload. Binding here first serves the inspectable separation of command structure and data.

State the actual verification scope

The experiment passed 381 checks. Of these, 360 compare complete SQL results with an independently computed PHP reference. They combine fifteen terms, four category choices, two orderings and three limits. The reference filters the original records, sorts them by the explicitly defined business rule and only then truncates the list.

Sixteen further cases reject invalid inputs. They include arrays, unknown categories, an SQL command offered as a sort choice, unsuitable number representations, control characters and invalid UTF-8. Five additional checks cover the default limit, unchanged fixture, integrity, the literal counterexample and propagated database exception. Every input and outcome is associated with the saved test report.

These numbers describe the actual experiment, not a success rate for arbitrary database applications. No HTTP endpoint, user login, tenant authorization or concurrent access was tested. Private records require an application to derive an authorized identity from a trusted context. A freely submitted category field would not provide that identity.

Choose the result shape deliberately too

The SELECT list names four needed columns explicitly. An internal notes column added later would not automatically join the returned array. This is useful for interfaces whose output might otherwise grow unnoticed over time. The selected contents still need assessment before use. An explicit projection is an inspectable limit, not a complete authorization system.

A successfully read title remains data content. A later web view would need to handle its actual HTML context correctly. A prepared query does not perform that job. The tested component returns arrays and contains no browser rendering. An interface built on it therefore needs its own checks for text output, errors and interaction. Associate verification with the layer actually exercised.

Do not accept extensions as arbitrary SQL fragments

A future multiple-category filter would change the input contract. One placeholder does not simply accept an arbitrary list of SQL values. First define the maximum list length, permitted entries and meaning of an empty selection. Each required value can then receive an appropriate parameter position. This small function deliberately accepts no such list; a category array is among its tested rejections.

A new option is not made safe by calling it a filter. Callers should not supply arbitrary WHERE expressions. Instead, the application defines concrete business options and translates each into fixed known structure. That also makes it easier to review whether several filters together still describe a sensible task. A growing collection of free-form fragments would lose that relationship again.

Finally, a detail view needs its own access decision. A private resource does not become available simply because its ID arrives as a correctly bound number. The ID describes the requested object. Permitted access comes from the trusted user context and application policy. That decision sits outside this exclusively public artificial catalogue. Safe value passing and authorized data access complement one another.

Checklist for the next PDO operation

• Define business filters, search meaning and result ordering.
• Keep SQL structure visibly separate from outside data values.
• Use placeholders for complete values rather than column names.
• Select variable orderings through a small fixed mapping.
• Validate business types and limits before binding.
• Give LIKE characters the meaning intended by the product.
• Provide deterministic tie-breaking for equal sort values.
• Investigate Unicode and collation with appropriate cases.
• Keep invalid input and database failures distinct from empty success.
• Configure connection privileges and business authorization separately.
• Verify driver differences before adopting the code elsewhere.
• Base performance claims on suitable measurements rather than an API name.