Avoid SQL injection in php

How to avoid sql injection in php .
Is there some function similar to mysql_real_escape_string()

Database: Oracle
Scripting language: PHP
OS : Linux
LVL 15
Who is Participating?
TvMptConnect With a Mentor Commented:
One of the first defense against injection should be binding variables

oci_bind_by_name — Binds a PHP variable to an Oracle placeholder

Bind variables should be extensively used for both security and performance reasons.

This article will help you
Patrick BogersConnect With a Mentor Datacenter platform engineer LindowsCommented:

It is all about sanitizing user input.
There are more then one ways to inject sql code, please read more about it here.
InsoftserviceAuthor Commented:
Thanx for ur post.

$sql = "select * from test";

if(!$query->ora_query($DB, $sql))
echo "error";

function ora_query(&$db, $query="")
      $this->stmt = OCIParse($db->connect_id, $query);
      if (!$this->stmt)
            return false;
      if (OCIExecute($this->stmt))
            return $this->stmt;
      //print "SQL = $query<br>\n";
      return FALSE;
      return FALSE;


Where DB is database connection .
$db->connect_id is connection id .
Please do let me know how to add oci_bind to such query .
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

slightwv (䄆 Netminder) Commented:
You use bind variables when you have dynamic SQL.  "select * from test" is static.  There is ZERO chance for SQL injection.

Now if the SQL was: "select * from test where some_col =" + some_parameter, you are open to sql injection because I might be able to add SQL to the string passed in as some_parameter.

For example, you have a text box where I am 'supposed' to enter a number and I enter:
1 or 1=1.

The select statement becomes:
select * from test where some_col = 1 or 1=1

What comes back form the database?

Bind variables would be something like:
"select * from test where some_col = :myBindVar";

You then set a bind value that resolves when the SQL is executed.

Sorry I don't know PHP so I might have some syntax wrong.
Basic example Oracle Select with PHP using Bind Variables

// define some constants
define("DB_USER", "username");
define("DB_PASS", "password");
define("DB", "//host:1523/sid");
// connect to oracle
if(!$con = oci_pconnect(DB_USER, DB_PASS, DB)) {
  echo "Cannot connect to database.";
// sql with bind variable
$sql = "select field_a from table_a where field_c = :somevalue_a";
$parse_sql = oci_parse($con, $sql);
$somevariable_a = "some value";
// bind the sql variable to php variable
oci_bind_by_name($parse_sql, ":somevalue_a", $somevariable_a);
// fetch the results and loop through
while ($row = oci_fetch_assoc($parse_sql)) {
  // grab the contents of the field
  $field_a = $row['FIELD_A'];

Open in new window

InsoftserviceAuthor Commented:
sorry its just not $sql = "select * from test";
but there are many other condition which is applied.

if condition x satisfy
   $where = " and tbl1.filed =   ".$val;
else if condition y satisfy
   $where = " and tbl1.filed =   ".$val2;

$sql = "select * from test where 1=1 ".$where;
if(!$query->ora_query($DB, $sql))
echo "error";

above code is just an algo an not exact query.

Please note the above function are used nearly all pages.
I want such function or script by which if i change in my function ora_query() will resolve my issue as i don't have to do changes in all query.
As it will take me month to do such changes in my whole project
slightwv (䄆 Netminder) Commented:
>>I want such function or script by which if i change in my function ora_query() will resolve my issue

Once the query has been created, I don't think there is a way to check to see if SQL has been injected.

Using your example above, if I assign $val2 to:  "1 or 1=1", what are you thinking you can to in ora_query to stop it from returning ALL rows?
InsoftserviceAuthor Commented:
@slightwv i just want to scan the input sent by client.  like $$val2 and $val1 .
I want to avoid sql injection in oracle .
According to above suggestion it requires sql bind.
But my problem is that i can't do for all query as there are lots of query and i can't do it on all query .
So i wanted the script which could handle this by calling just one function  within my function written above.

I hope now my issue is clear to all
slightwv (䄆 Netminder) Commented:
>>I want to avoid sql injection in oracle .

To avoid SQL injection, you need a way to keep users/apps from executing SQL you don't want.

There is no way to inspect a string to 'validate' it right before you execute it. to see if injection has happened.

Given what I've explained about how SQL injection can happen, what are you thinking is possible?
InsoftserviceAuthor Commented:
searched string for employee no via input text field.

$empno =  1'or'1'='1'

echo $sql  = "select * from emptable where 1=1 and empno = '$empno'";
o/p of query select * from emptable where 1=1 and empno =  '1'or'1'='1'

and it gives o/p of whole db.
Basic example of sql injection.
My question
 1 > how to avoid such data o/p in oracle as we do in mysql mysql_real_escape_string.
 2 > we have bunch of query so is there some simple way so that it can be done to all queries by just changing one function.
for above query i had use oci_bind_by_name() . but it failed
slightwv (䄆 Netminder) Commented:
>as we do in mysql mysql_real_escape_string

From what I read about this function all it does is add an escape character to certain characters.

This really isn't protection from SQL injection.

For example: "1 or 1=1" doesn't have any characters to escape so mysql_real_escape_string will not do anything and yet, it will still return the entire table.

To avoid this form of SQL injection, you simply cannot do string concatenation to build a query.

The main character that function goes after is the single quote.  The problem with a blanket replace of this character is it can mess up any query passed in that has strings in the where clause:  select * from dual where dummy='X';

The single quotes are necessary in the above query.
InsoftserviceAuthor Commented:
So, please suggest what has to be done to avoid it.
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>So, please suggest what has to be done to avoid it.

We have:  Bind variables.

You need to avoid dynamic SQL by string concatenation.  Until you do that, there is no way to ensure any string concatenated together will not introduce injection.

You can always change the apps to help limit the ability by disallowing certain characters/phrases in the input fields but it will still not eliminate it.
InsoftserviceAuthor Commented:
@slightwv really thanks for the support.
But it would be great if you  could provide me examples of each if possible for bind.
As i have tried oci_bind but it failed i might have done some mistake.
Please help me out of this
slightwv (䄆 Netminder) Commented:
Sorry.  I'm not a php person.

There are many examples out there on php and bind variables:
InsoftserviceAuthor Commented:
oci_bind did the work,but had to sanitize the php code
All Courses

From novice to tech pro — start learning today.