Imagine you're building a powerful search application. Users type in keywords, and your backend needs to query the Manticore Search database to find matching results. A common (and tempting!) approach is to embed user input directly into your SQL queries. For example, you might filter by a numeric field such as a category or record ID. If the user passes a normal value like 5, the query is SELECT * FROM products WHERE id=5. But what if they pass 1 OR 1=1? The query becomes SELECT * FROM products WHERE id=1 OR 1=1 — the condition is always true, so the query returns every row instead of one. This is SQL injection.
Fortunately, there's a safer and more efficient way: prepared statements. Essentially, prepared statements separate your SQL code from the data you pass in. Instead of building the entire query string each time, you define the query structure once with placeholders and then supply the search terms separately. You can learn more about the concept on Wikipedia .
Manticore Search supports prepared statements over the standard MySQL protocol, giving you a powerful tool for building secure search applications. By using prepared statements, you'll not only dramatically reduce the risk of SQL injection, but you'll also improve the readability of your code.
Prepared statements aren't just a feature; they're sometimes a requirement. For example, the Rust sqlx library works with the MySQL endpoint solely using prepared statements. Also, some OLE DB connectors that enable MS SQL to work with a MySQL server use prepared statements internally.
Why Use Prepared Statements?
Security First (SQL Injection): SQL injection is a web security vulnerability that allows attackers to interfere with the queries an application makes to its database. It happens when user input is improperly incorporated into a SQL query, allowing malicious code to be executed. For example, consider a simple search query built by concatenating a user's search term directly into the SQL:
// Vulnerable code example (DO NOT USE!)
$productId = $_GET['search'];
$query = "SELECT * FROM products WHERE id= " . $productId;
If $productId contained something like 0 OR 1=1, the query would become SELECT * FROM products WHERE id= 0 OR 1=1, effectively bypassing the WHERE clause and returning all products.
Prepared statements prevent this by treating user input strictly as data, not as part of the SQL command itself. The database driver handles the escaping and quoting, ensuring that any potentially harmful characters are neutralized. Here's the same query using a prepared statement:
// Secure code example using a prepared statement
$productId = $_GET['search'];
$stmt = $mysqli->prepare("SELECT * FROM products WHERE id= ?");
$stmt->bind_param("i", $productId);
$stmt->execute();
In this case, even if $productId contains malicious code, it will be treated as a literal value, not executable SQL.
How They Work
Prepared statements operate using a simple three-step process:
- Prepare: First, you send the SQL statement with placeholders (like
?or?VEC?) to Manticore Search. Manticore parses this statement and creates a query plan. It then returns a unique identifier for this prepared statement. - Bind: Next, you send the actual data – the values for the placeholders – to Manticore separately. This is where the security comes in; the data is treated purely as data, not as SQL code.
- Execute: Finally, you instruct Manticore to execute the prepared statement using the stored query plan and the bound parameters.
Think of it like creating a template. You build the structure once, then fill in the blanks with different information each time you need to use it.
Parameter Placeholders: ? & ?VEC?
Manticore Search uses specific placeholders to identify parameters within your prepared statements:
?represents a single parameter – this could be an integer, a floating-point number, or a string. When using this placeholder, Manticore automatically handles escaping and quoting for string values, protecting against SQL injection and ensuring proper data formatting.?VEC?is designed for lists of numeric values. It expects a string containing numbers separated by commas and optional spaces (e.g.,1, 2.3, 4, 1e-10, INF). Crucially, no escaping or quoting is applied to the values within?VEC?. Valid input consists solely of numbers, commas, and spaces; any other characters will likely result in an error. This makes it perfect for directly inserting numeric vectors into your data - both float vectors and integer MVAs (multi-value attributes).
Example: prepared statements in PHP
Let's see how prepared statements work in practice using PHP. We'll demonstrate both a simple insert with string values and a more complex insert involving a floating-point vector using the ?VEC? placeholder.
First, a basic insertion:
<?php
// Assuming you have a valid MySQLi connection established ($mysqli)
$stmt = $mysqli->prepare("INSERT INTO products (name, description) VALUES (?, ?)");
$productName = "Awesome Widget";
$productDescription = "A truly amazing widget for all your needs.";
$stmt->bind_param("ss", $productName, $productDescription); // "ss" indicates two strings
$stmt->execute();
echo "Product added successfully!";
?>
This code prepares the INSERT statement, binds the string values for the product name and description, and then executes the query. The resulting SQL executed by Manticore would be:
INSERT INTO products (name, description) VALUES ('Awesome Widget', 'A truly amazing widget for all your needs.');
Now, let's tackle an example using a float vector. What is ?VEC?? It is a placeholder (only used in prepared statements) for a vector — a list of numbers, e.g. for embeddings or similar data. In Manticore SQL, a vector literal is always written with parentheses: (0.1, 0.2, 0.3). So when you use a prepared statement and have a vector parameter, you write those parentheses in the SQL string and use ?VEC? where the numbers go. You bind only the comma-separated numbers (e.g. "0.1,0.2,0.3"); you do not bind the ( and ) — they stay in the query. Without prepared statements you would build the full literal (0.1, 0.2, 0.3) yourself in the query string.
In PHP mysqli, the usual way to bind ?VEC? values is as strings, so iss is the normal choice in this example. If you want to stream a larger vector payload, you can also bind the parameter as b and send the contents with send_long_data().
<?php
// Assuming you have a valid MySQLi connection established ($mysqli)
$stmt = $mysqli->prepare("INSERT INTO items (item_id, coords, features) VALUES (?, (?VEC?),(?VEC?))");
$itemId = 123;
$coordVector = "20.245,54.354,30.000"; // that is vector of floats
$featureSet = "1,4,20,456,112,3"; // that is set of integer values (MVA)
$stmt->bind_param("iss", $itemId, $coordVector, $featureSet); // "i" for integer (itemId), "s" for string
$stmt->execute();
echo "Item with feature vector added successfully!";
$itemId = 124;
$coordVector = "18.500,42.000,31.125"; // Another float vector
$featureSet = "0,6,34,665,22,3445,221,564,2232,5644,43"; // Example with more feature values
// For larger payloads you can bind the second ?VEC? as a blob and stream it.
$featurePlaceholder = "";
$stmt->bind_param("isb", $itemId, $coordVector, $featurePlaceholder); // "b" is for blob data
// bind_param() must be called before send_long_data().
$stmt->send_long_data(2, $featureSet); // zero-based index: 2 means the third bound parameter
$stmt->execute();
echo "Item with feature vector added successfully!";
?>
Notice that the parentheses are part of the SQL string in the prepare() call. We only bind the values within the parentheses using the ?VEC? placeholder. The resulting SQL executed by Manticore will be:
INSERT INTO items (item_id, coords, features) VALUES (123, (20.245,54.354,30.000), (1,4,20,456,112,3));
INSERT INTO items (item_id, coords, features) VALUES (124, (18.500,42.000,31.125), (0,6,34,665,22,3445,221,564,2232,5644,43));
Using ?VEC? in a prepared statement gives you the same benefits as with the ? placeholder: the vector values are sent as data, not as part of the SQL text, so they cannot be interpreted as SQL and cannot cause injection. You also avoid having to manually build or escape the vector literal in your application — Manticore receives the bound numbers and formats the vector correctly, which keeps the query safe and the data consistent.
Important Considerations & Limitations
While powerful, Manticore's prepared statements have a few limitations to keep in mind.
Multi-Queries: Only a single SQL statement is allowed per prepared statement. Attempts to use multi-queries (e.g., SELECT ...; SHOW META) will fail. If you need to execute multiple statements, prepare a separate statement for each one and execute them sequentially within the same session.
Numeric Types: Some database drivers (like mysql2 for Node.js) might send numeric parameters as DOUBLE by default. This could lead to unexpected behavior if you require strict integer behavior (like rejecting negative IDs). In such cases, consider sending integers as strings or utilize driver-specific integer types (e.g., BigInt) to ensure correct data handling.
Rust sqlx Users: If you're using the sqlx crate in Rust, be aware that when reading result set rows, you must use column indices rather than column names. While column names are present in the result set, sqlx doesn't utilize them for mapping. For example, use row.try_get(0)? instead of row.try_get("id")?.
Conclusion
Prepared statements offer a critical combination of security, readability, and potential performance gains when working with Manticore Search. By separating your SQL logic from your data, you dramatically reduce the risk of SQL injection attacks, improve code maintainability, and potentially speed up query execution. We strongly encourage you to adopt prepared statements in your Manticore Search applications.
For more in-depth information, be sure to consult these resources:
- Manticore Search Documentation on Prepared Statements: https://manual.manticoresearch.com/Connecting_to_the_server/MySQL_protocol#Prepared-statements
- Wikipedia - Prepared Statements: https://en.wikipedia.org/wiki/Prepared_statement
This guide provides a solid foundation for using prepared statements effectively in your Manticore Search projects, leading to more secure, efficient, and maintainable applications.