Solved

Avoid SQL injection in php

Posted on 2013-06-28
16
829 Views
Last Modified: 2013-07-06
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
Comment
Question by:Insoftservice
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 19

Assisted Solution

by:Patricksr1972
Patricksr1972 earned 150 total points
ID: 39283876
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
 
LVL 9

Accepted Solution

by:
TvMpt earned 150 total points
ID: 39283877
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
 
LVL 15

Author Comment

by:Insoftservice
ID: 39284015
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39284059
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
 
LVL 9

Expert Comment

by:TvMpt
ID: 39284098
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
 
LVL 15

Author Comment

by:Insoftservice
ID: 39285118
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39285233
>>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
 
LVL 15

Author Comment

by:Insoftservice
ID: 39285955
@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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39285960
>>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
 
LVL 15

Author Comment

by:Insoftservice
ID: 39289481
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39290487
>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
 
LVL 15

Author Comment

by:Insoftservice
ID: 39290875
So, please suggest what has to be done to avoid it.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 39290898
>>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
 
LVL 15

Author Comment

by:Insoftservice
ID: 39290946
@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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39291032
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
 
LVL 15

Author Closing Comment

by:Insoftservice
ID: 39304054
oci_bind did the work,but had to sanitize the php code
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now