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
Solved

Avoid SQL injection in php

Posted on 2013-06-28
16
868 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 20

Assisted Solution

by:Patrick Bogers
Patrick Bogers 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
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 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 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 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

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.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 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