[ACCEPTED]-Inserting data in oracle database using php-oracle

Never insert user input directly into SQL. Use 4 oci_bind_by_name() to prepare a secure statement. As a side 3 effect, that will also fix the error you're 2 getting (which is a quoting typo). The code 1 would 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);

You've got a few problems here. First, variables 16 aren't interpolated into strings enclosed 15 in single quotes. Try this simple script 14 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 13 need to escape the variables before using 12 them to construct an SQL query. A single 11 "'" character in any of the POST variables 10 will break your query, giving you an invalid 9 syntax error from Oracle.

Lastly, and perhaps 8 most importantly, I hope this is just example 7 code? You're using unfiltered user input 6 to construct an SQL query which leaves you 5 open to SQL injection attacks. Escaping 4 the variables will at least prevent the 3 worst kind of attacks, but you should still 2 do some validation. Never use 'tainted' data 1 to construct queries.

It's rather hard to say without seeing what 8 the generated SQL looks like, what charset 7 you are posting in and what charset the 6 database is using.

Splicing unfiltered user 5 content into an SQL statement and sending 4 it to the DB is a recipe for disaster. While 3 other DB APIs in PHP have an escape function, IIRC 2 this is not available for Oracle - you should 1 use data binding.

C.

You need single quotes around the varchar fields 24 that you are inserting (which I presume 23 are url_name, anchor_text, and description). The 22 single quote that you currently have just 21 make those values a String but in Oracle, varchar 20 fields need to have single quotes around 19 them. Try this:

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

I don't have PHP anywhere 18 to test it, but that should create the single 17 quotes around your values.

Because really 16 the sql you will eventually be executing 15 on the database would look like this:

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

The 14 main article Binding Variables in Oracle and PHP appears to be down but here 13 is the Google Cache Version that goes into detail about how 12 to bind variables in PHP. You definitely 11 want to be doing this for 1) performance 10 and 2) security from SQL injection.

Also, my 9 PHP is a bit rusty but looks like you could 8 also do your original query statement like 7 this:

$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 6 quotes that may be present in the data you 5 receive from your form variables. In an 4 Oracle sql string you need to convert single 3 quotes to 2 single quotes to escape them. See 2 the section here titled "How can I insert 1 strings containing quotes?"

It's because you have un-quoted quote characters 1 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}'.\")";

If you are still in starting developing, I 11 want to suggest to use AdoDB instead of oci_ functions 10 directly.

Your code above can be rewritten 9 using AdoDB like this:

<?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 8 need to make an associative array, with 7 the column name as key, and then assign 6 the value for the correct column. Data sanitation 5 is handled by AdoDB automatically, so you not 4 have to do it manually for each column.

AdoDB is 3 multi-database library, so you can change 2 the databas enginge with a minimal code 1 change in your application.