Inserting data in oracle database using php


Question

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





?>
1
6
1/22/2010 5:53:31 PM

Accepted Answer

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

$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);
14
12/18/2012 11:27:01 PM

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.

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.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon