oci_bind_by_name和to_date PHP/OCI/Oracle [英] oci_bind_by_name and to_date PHP/OCI/Oracle

问题描述

我有以下内容:

    $ARTIFACT_NAME = $_POST['ArtifactName'];
    $ARTIFACT_TYPE = $_POST['ArtifactType'];
    $ARTIFACT_LOCATION = $_POST['ArtifactLocation'];
    $ARTIFACT_DOMAIN = $_POST['ArtifactDomain'];
    $ARTIFACT_AUTHOR = $_POST['ArtifactAuthor'];
    $ARTIFACT_LABEL = 'DB_'.$ARTIFACT_LOCATION.'_'.$ARTIFACT_DOMAIN.'_'.$ARTIFACT_NAME;
    $AUDIT_CONSTRAINTS = $_POST['AuditConstraints'];
    $SECURITY_CONSTRAINTS = $_POST['SecurityConstraints'];
    $REGISTERED_EMAIL = $_SERVER['HTTP_REMOTE_USER'];
    $REGISTERED_TIMESTAMP = "to_date('15-08-2011 14:32:37', 'DD-MM-YYYY HH24:MI:SS')";

    $query =    "INSERT INTO ".$db_schema.".ARTIFACTS (ARTIFACT_ID, ARTIFACT_NAME, ARTIFACT_TYPE, ARTIFACT_LOCATION, ARTIFACT_DOMAIN, ARTIFACT_AUTHOR, ARTIFACT_LABEL, AUDIT_CONSTRAINTS, SECURITY_CONSTRAINTS, REGISTERED_EMAIL, REGISTERED_TIMESTAMP)
                VALUES (:bind1, :bind2, :bind3, :bind4, :bind5, :bind6, :bind7, :bind8, :bind9, :bind10, :bind11)";
    $statement = oci_parse($connection, $query);

    oci_bind_by_name($statement, ":bind1", $ARTIFACT_ID);
    oci_bind_by_name($statement, ":bind2", $ARTIFACT_NAME);
    oci_bind_by_name($statement, ":bind3", $ARTIFACT_TYPE);
    oci_bind_by_name($statement, ":bind4", $ARTIFACT_LOCATION);
    oci_bind_by_name($statement, ":bind5", $ARTIFACT_DOMAIN);
    oci_bind_by_name($statement, ":bind6", $ARTIFACT_AUTHOR);
    oci_bind_by_name($statement, ":bind7", $ARTIFACT_LABEL);
    oci_bind_by_name($statement, ":bind8", $AUDIT_CONSTRAINTS);
    oci_bind_by_name($statement, ":bind9", $SECURITY_CONSTRAINTS);
    oci_bind_by_name($statement, ":bind10", $REGISTERED_EMAIL);
    oci_bind_by_name($statement, ":bind11", $REGISTERED_TIMESTAMP);

哪个出现以下错误:

ORA-01858: a non-numeric character was found where a numeric was expected

但是，如果我不绑定$REGISTERED_TIMESTAMP并将to_date直接插入到$query中-则效果很好.

However, if i just don't bind $REGISTERED_TIMESTAMP and insert the to_date into the $query directly - it works perfectly.

这是怎么回事?！这让我发狂！

What's going on?! This is drving me mad!

推荐答案

您正在使用带有绑定参数的Oracle语句.这样很好，因为它可以防止在危险代码插入到SQL语句中的情况下进行SQL注入.但是，在这种情况下，它会阻止执行TO_CHAR函数.相反，它将尝试将整个字符串转换为时间戳，这当然是行不通的.

You're using an Oracle statement with bound parameters. That's good because it prevents SQL injections where dangerous code is inserted into your SQL statement. However, in this case, it prevents the TO_CHAR function from being executed. Instead, it tries to convert the whole string into a timestamp, which of course doesnt' work.

解决方案非常简单:将TO_CHAR函数从绑定参数直接移到语句中:

The solution is rather straight-forward: move to TO_CHAR function away from the bound parameter directly into the statement:

$REGISTERED_TIMESTAMP = "15-08-2011 14:32:37";

$query =    "INSERT INTO ".$db_schema.".ARTIFACTS (ARTIFACT_ID, ARTIFACT_NAME, ARTIFACT_TYPE, ARTIFACT_LOCATION, ARTIFACT_DOMAIN, ARTIFACT_AUTHOR, ARTIFACT_LABEL, AUDIT_CONSTRAINTS, SECURITY_CONSTRAINTS, REGISTERED_EMAIL, REGISTERED_TIMESTAMP)
            VALUES (:bind1, :bind2, :bind3, :bind4, :bind5, :bind6, :bind7, :bind8,
               :bind9, :bind10, to_date(:bind11, 'DD-MM-YYYY HH24:MI:SS'))";

这篇关于oci_bind_by_name和to_date PHP/OCI/Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！