使用oci_8在oracle数据库中插入数据。插入带有特殊字符或引号的字符串的示例查询
update TABLENAME set COMMENTS = 'As per Mark's email dated 28-Feb-2015 - Bill Gates & Team's effort' where ID = 99;
插入/更新
$query = 'update TABLENAME set COMMENTS = '$_POST[comments]';
$result = customexecute($new_query);
public function customexecute($query)
{
$resutlt = parent::customquery($query);
return $resutlt;
}
public static function customquery($query)
{
try{
$stmt = oci_parse($conn, $query);
oci_execute($stmt,OCI_COMMIT_ON_SUCCESS);
oci_commit(db_singleton::getInstance());
oci_free_statement($stmt);
}catch (Exception $e)
{
print_r($e);
}
}
在ORACLE数据库上执行它时，它说SQl command not properly ended.查看了这里提到的Parameterized queries，但无法成功地集成它。
$query = 'UPDATE tablename SET field = :field WHERE id = :id';
$stmt = oci_parse($oracleConnection, $query);
oci_bind_by_name($stmt, ':field', "The field value with 'apostrophes' and so");
oci_bind_by_name($stmt, ':id', '125');
$result = oci_execute($stmt);
我可以在我的控制器中的查询中传递:bind_comments。但是$stmt驻留在我的db_singleton文件中（对于所有db查询是通用的），并且不能单独传递给单个查询。
如何净化用户输入或不允许在创建SQL代码时使用数据
从更新函数，将所需的一切传递给执行函数：
$result = customExecute(
'update xxx set comments=:COMMENTS where id=:ID',
[
':COMMENTS' => $_POST['comment'],
':ID' => 99
]
);
然后在execute函数中简单地迭代数组以绑定所有参数：
public static function customExecute($sql, array $params = [])
{
$stmt = oci_parse($conn, $sql);
foreach ($params as $key => &$value) {
oci_bind_by_name($stmt, $key, $value);
}
$result = oci_execute($stmt);
...
}
不，不出所料，MySQL函数无法与Oracle数据库一起使用：）
您需要参数化事物，例如：
$query = 'update TABLENAME set COMMENTS = :bind_comments where id = :bind_id';
$stmt = $dbh->prepare($query);
$stmt->bindParam(':bind_comments', $_POST['comments']);
$stmt->bindParam(':bind_id', $_POST['id']);
$stmt->execute();
使用OCI8 PHP扩展的正确方法是：
$query = 'UPDATE tablename SET field = :field WHERE id = :id';
$stmt = oci_parse($oracleConnection, $query);
oci_bind_by_name($stmt, ':field', "The field value with 'apostrophes' and so");
oci_bind_by_name($stmt, ':id', '125');
$result = oci_execute($stmt);
更多信息：http://php.net/manual/book.oci8.php