Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1032
  • Last Modified:

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
0
Insoftservice
Asked:
Insoftservice
  • 7
  • 6
  • 2
  • +1
3 Solutions
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi,

It is all about sanitizing user input.
There are more then one ways to inject sql code, please read more about it here.
0
 
TvMptCommented:
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
http://www.integrigy.com/files/Integrigy_Oracle_SQL_Injection_Attacks.pdf
0
 
InsoftserviceAuthor Commented:
Thanx for ur post.

$sql = "select * from test";

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

function ora_query(&$db, $query="")
{
   if($query!=""&&$db->connect_id)
    {
      $this->stmt = OCIParse($db->connect_id, $query);
      if (!$this->stmt)
      {
            return false;
      }
      if (OCIExecute($this->stmt))
      {
            $db->addstmt($this->stmt);
            return $this->stmt;
      }
      OCIFreeStatement($this->stmt);
      //print "SQL = $query<br>\n";
      return FALSE;
     }
      else
      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 .
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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.
0
 
TvMptCommented:
Basic example Oracle Select with PHP using Bind Variables

<?php
 
// 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.";
  exit(9);
}
 
// 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);
oci_execute($parse_sql);
 
// 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

0
 
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
0
 
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?
0
 
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
0
 
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?
0
 
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
0
 
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.
0
 
InsoftserviceAuthor Commented:
So, please suggest what has to be done to avoid it.
0
 
slightwv (䄆 Netminder) 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.
0
 
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
0
 
slightwv (䄆 Netminder) Commented:
Sorry.  I'm not a php person.

There are many examples out there on php and bind variables:
http://php.net/manual/en/function.oci-bind-by-name.php
0
 
InsoftserviceAuthor Commented:
oci_bind did the work,but had to sanitize the php code
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now