php 使用php在oracle数据库中插入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2119145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting data in oracle database using php
提问by user256938
The following code is generating this
下面的代码正在生成这个
Warning: oci_execute() [function.oci-execute]:
ORA-00911: invalid character in F:\wamp\www\SEarch Engine\done.php on line 17
the code is...
代码是...
<?php
include_once('config.php');
$db = oci_new_connect(ORAUSER,ORAPASS,"localhost/XE");
$url_name=$_POST['textfield'];
$keyword_name=$_POST['textarea'];
$cat_news=$_POST['checkbox'];
$cat_sports=$_POST['checkbox2'];
$anchor_text=$_POST['textfield2'];
$description=$_POST['textarea2'];
$sql1="insert into URL(Url_ID,Url_Name,Anchor_Text,Description)
VALUES( 9,".'{$url_name}'.",".'{$anchor_text}'.",".'{$description}'.")";
$result=oci_parse($db,$sql1);
oci_execute($result);
?>
回答by Max Shawabkeh
Never insert user input directly into SQL. Use oci_bind_by_name()to prepare a secure statement. As a side effect, that will also fix the error you're getting (which is a quoting typo). The code would look like
切勿将用户输入直接插入 SQL。使用oci_bind_by_name()准备安全语句。作为副作用，这也将解决您遇到的错误（这是一个引用错字）。代码看起来像
$url_name = $_POST['textfield'];
$anchor_text = $_POST['textfield2'];
$description = $_POST['textfield3'];
$sql = 'INSERT INTO URL(Url_ID,Url_Name,Anchor_Text,Description) '.
'VALUES(9, :url, :anchor, :description)';
$compiled = oci_parse($db, $sql);
oci_bind_by_name($compiled, ':url', $url_name);
oci_bind_by_name($compiled, ':anchor', $anchor_text);
oci_bind_by_name($compiled, ':description', $description);
oci_execute($compiled);
回答by Paul Osman
You've got a few problems here. First, variables aren't interpolated into strings enclosed in single quotes. Try this simple script to see what I mean:
你这里有一些问题。首先，变量不会被插入到用单引号括起来的字符串中。试试这个简单的脚本，看看我的意思：
$a = 'hi';
print 'Value: $a'; // prints 'Value: $a'
vs.
对比
$a = 'hi';
print "Value: $a"; // prints 'Value: hi'
Secondly, you'll need to escape the variables before using them to construct an SQL query. A single "'" character in any of the POST variables will break your query, giving you an invalid syntax error from Oracle.
其次，您需要在使用变量构建 SQL 查询之前对其进行转义。任何 POST 变量中的单个“'”字符都会破坏您的查询，从而导致 Oracle 出现无效的语法错误。
Lastly, and perhaps most importantly, I hope this is just example code? You're using unfiltered user input to construct an SQL query which leaves you open to SQL injection attacks. Escaping the variables will at least prevent the worst kind of attacks, but you should still do some validation. Never use 'tainted' data to construct queries.
最后，也许是最重要的，我希望这只是示例代码？您正在使用未经过滤的用户输入来构建 SQL 查询，这让您容易受到 SQL 注入攻击。转义变量至少可以防止最糟糕的攻击，但您仍然应该做一些验证。永远不要使用“受污染”的数据来构建查询。
回答by Donny Kurnia
If you are still in starting developing, I want to suggest to use AdoDBinstead of oci_functions directly.
如果你还在开始开发，我想建议你使用AdoDB而不是oci_直接使用函数。
Your code above can be rewritten using AdoDBlike this:
上面的代码可以使用AdoDB重写，如下所示：
<?php
include_once('config.php');
$url_name=$_POST['textfield'];
$keyword_name=$_POST['textarea'];
$cat_news=$_POST['checkbox'];
$cat_sports=$_POST['checkbox2'];
$anchor_text=$_POST['textfield2'];
$description=$_POST['textarea2'];
//do db connection
$adodb =& ADONewConnection("oci8://ORAUSER:[email protected]/XE");
if ( ! $adodb )
{
die("Cannot connect to database!");
}
//set mode
$adodb->SetFetchMode(ADODB_FETCH_BOTH);
//data for insert
$tablename = 'URL';
$data['Url_ID'] = 9;
$data['Url_Name'] = $url_name;
$data['Anchor_Text'] = $anchor_text;
$data['Description'] = $description;
$result = $adodb->AutoExecute($tablename, $data, 'INSERT');
if ( ! $result )
{
die($adodb->ErrorMsg());
return FALSE;
}
//reaching this line meaning that insert successful
In my code above, you just need to make an associative array, with the column name as key, and then assign the value for the correct column. Data sanitation is handled by AdoDBautomatically, so you not have to do it manually for each column.
在我上面的代码中，您只需要创建一个关联数组，以列名作为键，然后为正确的列分配值。数据清理由AdoDB自动处理，因此您不必为每一列手动执行此操作。
AdoDBis multi-database library, so you can change the databas enginge with a minimal code change in your application.
AdoDB是多数据库库，因此您可以在应用程序中以最少的代码更改来更改数据库引擎。
回答by symcbean
It's rather hard to say without seeing what the generated SQL looks like, what charset you are posting in and what charset the database is using.
如果没有看到生成的 SQL 是什么样子、您发布的字符集以及数据库使用的字符集，就很难说。
Splicing unfiltered user content into an SQL statement and sending it to the DB is a recipe for disaster. While other DB APIs in PHP have an escape function, IIRC this is not available for Oracle - you should use data binding.
将未过滤的用户内容拼接到 SQL 语句中并将其发送到数据库是灾难的秘诀。虽然 PHP 中的其他 DB API 具有转义功能，但 IIRC 不适用于 Oracle - 您应该使用数据绑定。
C.
C。
回答by Doug Porter
You need single quotes around the varcharfields that you are inserting (which I presume are url_name, anchor_text, and description). The single quote that you currently have just make those values a String but in Oracle, varchar fields need to have single quotes around them. Try this:
您需要在varchar要插入的字段周围加上单引号（我认为它们是 url_name、anchor_text 和 description）。您当前拥有的单引号只是将这些值设为字符串，但在 Oracle 中，varchar 字段需要在它们周围加上单引号。尝试这个：
$sql1="insert into URL(Url_ID,Url_Name,Anchor_Text,Description) VALUES( 9,'".'{$url_name}'."','".'{$anchor_text}'."','".'{$description}'."')";
I don't have PHP anywhere to test it, but that should create the single quotes around your values.
我没有任何地方可以测试 PHP，但这应该会在您的值周围创建单引号。
Because really the sql you will eventually be executing on the database would look like this:
因为实际上您最终将在数据库上执行的 sql 看起来像这样：
insert into URL
(
Url_ID,
Url_Name,
Anchor_Text,
Description
)
VALUES
(
9,
'My Name',
'My Text',
'My Description'
)
The main article Binding Variables in Oracle and PHPappears to be down but here is the Google Cache Versionthat goes into detail about how to bind variables in PHP. You definitely want to be doing this for 1) performance and 2) security from SQL injection.
主要文章Oracle 和 PHP中的绑定变量似乎已关闭，但这里是Google 缓存版本，详细介绍了如何在 PHP 中绑定变量。您肯定希望这样做是为了 1) 性能和 2) SQL 注入的安全性。
Also, my PHP is a bit rusty but looks like you could also do your original query statement like this:
另外，我的 PHP 有点生疏，但看起来您也可以像这样执行原始查询语句：
$sql1="insert into URL(Url_ID,Url_Name,Anchor_Text,Description) values ( 9, '$url_name', '$anchor_text', '$description')";
Edit
Also, you need to escape any single quotes that may be present in the data you receive from your form variables. In an Oracle sql string you need to convert single quotes to 2 single quotes to escape them. See the section heretitled "How can I insert strings containing quotes?"
编辑
此外，您需要转义从表单变量接收的数据中可能存在的任何单引号。在 Oracle sql 字符串中，您需要将单引号转换为 2 个单引号以对它们进行转义。请参阅此处标题为“如何插入包含引号的字符串？”的部分。
回答by wallyk
It's because you have un-quoted quote characters in the query string. Try this instead:
这是因为您在查询字符串中有未加引号的引号字符。试试这个：
$sql1="insert into URL(Url_ID,Url_Name,Anchor_Text,Description)
VALUES( 9,\".'{$url_name}'.\",\".'{$anchor_text}'.\",\".'{$description}'.\")";
