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.

Navicat



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.

dalmp


comments powered by Disqus