/usr/share/php/kohana3.1/modules/database/guide/database/query/prepared.md is in libkohana3.1-mod-database-php 3.1.5-1.1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | # Prepared Statements
Using prepared statements allows you to write SQL queries manually while still escaping the query values automatically to prevent [SQL injection](http://wikipedia.org/wiki/SQL_Injection). Creating a query is simple:
$query = DB::query(Database::SELECT, 'SELECT * FROM users WHERE username = :user');
The [DB::query] method is just a shortcut that creates a new [Database_Query] class for us, to allow method chaining. The query contains a `:user` parameter, which we will get to in a second.
The first parameter of [DB::query] is the type of query. It should be `Database::SELECT`, `Database::INSERT`, `Database::UPDATE`, or `Database::DELETE`. This is done for compatibility reasons for drivers, and to easily determine what `execute()` should return.
The second parameter is the query itself. Rather than trying to concatenate your query and variables together, you should make use of [Database_Query::param]. This will make your queries much easier to mantain, and will escape the values to prevent [SQL injection](http://wikipedia.org/wiki/SQL_Injection).
## Parameters
Our example query earlier contains a `:user` parameter, which we can assign to a value using [Database_Query::param] like so:
$query->param(':user', 'john');
[!!] Parameter names can be any unique string, as they are replaced using [strtr](http://php.net/strtr). It is highly recommended to **not** use dollars signs as parameter names to prevent confusion. Colons are commonly used.
You can also update the `:user` parameter by calling [Database_Query::param] again:
$query->param(':user', $_GET['search']);
If you want to set multiple parameters at once, you can use [Database_Query::parameters].
$query = DB::query(Database::SELECT, 'SELECT * FROM users WHERE username = :user AND status = :status');
$query->parameters(array(
':user' => 'john',
':status' => 'active',
));
It is also possible to bind a parameter to a variable, using a [variable reference]((http://php.net/language.references.whatdo)). This can be extremely useful when running the same query many times:
$query = DB::query(Database::INSERT, 'INSERT INTO users (username, password) VALUES (:user, :pass)')
->bind(':user', $username)
->bind(':pass', $password);
foreach ($new_users as $username => $password)
{
$query->execute();
}
In the above example, the variables `$username` and `$password` are changed for every loop of the `foreach` statement. When the parameter changes, it effectively changes the `:user` and `:pass` query parameters. Careful parameter binding can save a lot of code when it is used properly.
The only difference between `param()` and `bind()` is that `bind()` passes the variable by reference rather than by assignment (copied), so future changes to the variable can be "seen" by the query.
[!!] Although all parameters are escaped to prevent SQL injection, it is still a good idea to validate/sanitize your input.
## Display the raw query
If you want to display the SQL that will be executed, simply cast the object to a string:
echo Kohana::debug((string) $query);
// Should display:
// SELECT * FROM users WHERE username = 'john'
## Executing
Once you have assigned something to each of the parameters, you can execute the query using `execute()` and use [the results](results).
$result = $query->execute();
To use a different database [config group](config) pass either the name or the config object to `execute()`.
$result = $query->execute('config_name')
|