Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Avoid SQL injection in php

Posted on 2013-06-28
16
Medium Priority
?
967 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 23

Assisted Solution

by:Patrick Bogers
Patrick Bogers earned 450 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 450 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 77

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 77

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
 
LVL 77

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 77

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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 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 77

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

610 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