Insert data into oracle database using php

Question

The following code generates this

Warning: oci_execute() [function.oci-execute]: ORA-00911: invalid character in F:\wamp\www\SEarch Engine\done.php on line 17 

the code...

 <?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); ?> 

Answer 1

Never embed user input directly in SQL. Use oci_bind_by_name () to prepare a safe statement. As a side effect, the error you receive will also be fixed (this is a citation clause). The code will look like

 $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); 

Answer 2

You have a few problems here. First, variables are not interpolated to strings enclosed in single quotes. Try this simple script to understand what I mean:

 $a = 'hi'; print 'Value: $a'; // prints 'Value: $a' 

against.

 $a = 'hi'; print "Value: $a"; // prints 'Value: hi' 

Secondly, you will need to avoid variables before using them to build an SQL query. One "" character in any of the POST variables breaks your request, giving you the wrong Oracle syntax error.

Finally, and perhaps most importantly, I hope this is just sample code? You use unfiltered user input to build an SQL query that gives you the ability to open SQL injection attacks. Turning off the variables will at least prevent the worst type of attacks, but you should still do some checks. Never use "corrupted" data to build queries.

Answer 3

It’s hard to say without seeing what the generated SQL looks like, what encoding you send and what encoding the database uses.

Linking unfiltered user content with an SQL statement and sending it to the database is a recipe for disaster. While other database APIs in PHP have an exit function, IIRC is not available for Oracle - you must use data binding.

FROM.

Answer 4

You need single quotes around the varchar fields you insert (I suppose these are url_name, anchor_text and description). The only quote you are currently using only makes these values ​​a string, but in Oracle, the varchar fields must have single quotes around them. Try the following:

 $sql1="insert into URL(Url_ID,Url_Name,Anchor_Text,Description) VALUES( 9,'".'{$url_name}'."','".'{$anchor_text}'."','".'{$description}'."')"; 

I don't have PHP to test, but this should create single quotes around your values.

Since indeed the sql that you end up running in the database would look like this:

 insert into URL ( Url_ID, Url_Name, Anchor_Text, Description ) VALUES ( 9, 'My Name', 'My Text', 'My Description' ) 

Main article Binding variables in Oracle and PHP are apparently omitted, but here's the Google Cache Version , which details how to bind variables in PHP. You definitely want to do this for 1) performance and 2) security against SQL injection.

Also, my PHP is a little rusty, but it looks like you can also make your original request as follows:

 $sql1="insert into URL(Url_ID,Url_Name,Anchor_Text,Description) values ( 9, '$url_name', '$anchor_text', '$description')"; 

Edit
In addition, you need to avoid any single quotes that may be present in the data that you receive from your form variables. In an Oracle string chain, you need to convert single quotes to 2 single quotes to avoid them. See the section here under the heading "How do I insert lines containing quotation marks?"

Answer 5

This is because you did not include quotation marks in the query string. Try instead:

 $sql1="insert into URL(Url_ID,Url_Name,Anchor_Text,Description) VALUES( 9,\".'{$url_name}'.\",\".'{$anchor_text}'.\",\".'{$description}'.\")"; 

Answer 6

If you are still starting development, I want to suggest using AdoDB instead of oci_ functions.

Your code above can be rewritten using AdoDB as follows:

 <?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: ORAPASS@127.0.0.1 /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 create an associative array with the column name as the key, and then assign a value to the correct column. Data sanitation is handled automatically by AdoDB , so you don’t need to do this manually for each column.

AdoDB is a library with several databases, so you can change database bindings with minimal code changes in the application.