Prepared statements¶
Prepared statements help you in many cases to avoid avoid mysql injections and helps increasing security of your queries by separating the SQL logic from the data being supplied.
DALMP\Database by default tries to determine the type of the data supplied, so you can just focus on your query without needing to specify the type of data, If you preffer you can manually specify the type of the data. The following table, show the characters which specify the types for the corresponding bind variables:
Character | Description |
---|---|
i | corresponding variable has type integer |
d | corresponding variable has type double |
s | corresponding variable has type string |
b | corresponding variable is a blob and will be sent in packets |
See also
Method prepare, & mysqli_stmt_bind_param.
To use “Prepared statements” on your SQL statements for retrieving data, the following methods can be used:
Name | Normal | Prepared statements | Cache Normal | Cache Prepared statements |
---|---|---|---|---|
all | GetAll | PGetAll | CacheGetAll | CachePGetAll |
assoc | GetAssoc | PGetAssoc | CacheGetAssoc | CachePGetAssoc |
col | GetCol | PGetCol | CacheGetCol | CachePGetCol |
one | GetOne | PGetOne | PGetOne | CacheGetOne |
row | GetRow | PGetRow | PGetRow | CacheGetRow |
Any query or either for Inserting or Updating:
Name | Normal | Prepared statements |
---|---|---|
Execute | Execute | PExecute |
Note
Notice that when using “Prepared statements” the methods are prefixed with a P.
See also
Method Cache.
Examples¶
1 2 3 4 5 6 7 8 9 10 11 12 | <?php
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
require_once 'dalmp.php';
$DSN = "utf8://$user:$password@127.0.0.1/test";
$db = new DALMP\Database($DSN);
$db->PExecute('SET time_zone=?', 'UTC');
|
Example using the LIKE statement:
1 2 3 4 5 | <?php
$sql = 'SELECT Name, Continent FROM Country WHERE Population > ? AND Code LIKE ?';
$rs = $db->FetchMode('ASSOC')->PGetAll($sql, 1000000, '%P%');
|
If you want to define the types, you must pass an array specifying each type. Example:
1 2 3 4 5 | <?php
$sql = 'SELECT * FROM mytable WHERE name=? AND id=?';
$rs = $db->FetchMode('ASSOC')->PGetAll($sql, array('s' => '99.3', 7));
|
An Insert example:
1 2 3 | <?php
$db->PExecute('INSERT INTO mytable (colA, colB) VALUES(?, ?)', rand(), rand());
|
See also
Method PExecute
An Update example:
1 2 3 | <?php
$db->PExecute('UPDATE Country SET code=? WHERE Code=?', 'PRT', 'PRT');
|
Warning
When updating the return value 0, Zero indicates that no records where updated.
Thanks Navicat for supporting Open Source projects.
A great amount of time has been spent creating, crafting and maintaining this software, please consider donating.
Donating helps ensure continued support, development and availability.
comments powered by Disqus