CODEWITHSUNDEEP
phpsqloracle-database
OschtärEi
OschtärEi

Reputation: 2265

Escaping ' in a query-string - is it even possible?

I have a large php application (purely php, no frameworks, etc.) which uses an oracle-db. All queries are executed like this:

oci_parse($conn_id,"insert into table (bla) values ('bla')");
oci_execute($stmt)

I know this is bad! No need pointing out stuff like "use bind" or something similar. I know that, but I can't change this.

What we all also know is that you have to escape characters. This question is especially about the ' charcter.

I have many queries like this:

$query = "INSERT INTO table (field1, field2,field3,field4) VALUES ('bla,bla','blub', 'mimi'm', 'mu's'c'hle')";
$query2 = "UPDATE table SET field1 =  'bla,bla', field2 = 'blub', field3 = 'mimi'm', field4 = 'mu's'c'hle' WHERE field5 = 'lol'zj'd'"

Sure, normally they do not have so many ' in it - but thats just for demonstration.

Now to the question: Is there any way to validate/escape the whole query-string in php? I can't think/find of a way to accomplish this, no matter how I think of it.

It's obvious that it's easy to escape all values before building the query-strings, by just replacing ' with '' - but is it possible when you only have the whole query as a string (like the examples above)? I personally can't think of an "universal solution"...

Upvotes: 2

Views: 1844

Answers (6)

itsid
itsid

Reputation: 811

Okay, this is DEFINETELY not failproof, or even elegant, but it does work on the given querys, as a "proof of concept" so to speak...

do not use the function in a production server.. it WILL break sooner (not later ;))

<?php


$query = "INSERT INTO table (field1, field2,field3,field4) VALUES ('bla,bla','blub','mimi'm','mu's'c'hle')";
$query2 = "UPDATE table SET field1 =  'bla,bla', field2 = 'blub', field3 = 'mimi'm', field4 = 'mu's'c'hle' WHERE field5 = 'lol'zj'd'";

function clean_given_query($qry)
{
    if(strpos($qry , " VALUES "))
    {   
        //the easy way, since we know exactly how many fields we have here
        $qra = explode('VALUES', $qry);
        if(count($qra) == 2)
        {
            // qra[0] = "INSERT INTO table (field1, field2,field3,field4)"
            // qra[1] = "('bla,bla','blub', 'mimi'm', 'mu's'c'hle')";
            $qtemp = explode('(', $qra[0]);
            $qtemp = $qtemp[1]; // we can loose the insert -part for now
            $fieldcount = count(explode(',',$qtemp)); // now we know how many fields we want to populate
            $qra[1] = explode("','", $qra[1]); // dirty values....
            if(count($qra[1]) === $fieldcount) //make sure we have the correkt value count
            {
                $values = array();
                foreach($qra[1] as $i => $val)
                {
                    if($i==0)
                        $val = substr($val, 3); // we know $val is a string and index 0 starts with (' which we need to remove!
                    if($i == count($qra[1])-1) // last item  needs to be cropped at the end
                        $val = substr($val, 0, count($val)-3); //also a string as we know.

                    $val = addslashes($val); //escape the string according to your needs
                    $values[] = $val;
                }
                return $qra[0]." VALUES ('".implode("','", $values)."')";
            }

        }
    }
    else if (strpos($qry, "SET"))
    {
        $qra = explode('=', $qry);
        // $qra[0] = "UPDATE table SET field1";
        // $qra[1] = "'bla,bla', field2";
        $save = $qra[0]."='";

        foreach($qra as $i => $mixed)
        {   
            if($i == 0) // qra[0]  holds nothing to edit!
                continue;

            $parts = explode(',', $mixed); // [0] 'bla    [1] bla'  [2] field2
            $nextfield = array_pop($parts);

            $val = implode(',', $parts); // $val = 'bla,bla'
            if(strpos($nextfield , "WHERE"))
            {
                list($val, $nextfield) = explode("WHERE",$nextfield);
                $nextfield = " WHERE ".$nextfield;
            }
            $val = trim($val);
            $val = substr($val, 1, count($val)-2); //$val  bla,bla
            $val = addslashes($val); // escape according to your needs

            if($val!=="" and strpos($nextfield , "WHERE") === false)
                $save .= $val."', ".$nextfield."='";
            elseif($val!=="" and strpos($nextfield , "WHERE"))
                $save .= $val."' ".$nextfield."='";
            else
            {
                $val = trim($nextfield);
                $val = substr($val, 1, count($val)-2); //$val  bla,bla
                $val = addslashes($val); // escape according to your needs
                $save .= $val."'";
            }
        }
        return $save;
    }
}

echo $query.PHP_EOL;
echo clean_given_query($query).PHP_EOL;

echo $query2.PHP_EOL;
echo clean_given_query($query2).PHP_EOL;

?>

Output:

INSERT INTO table (field1, field2,field3,field4) VALUES ('bla,bla','blub','mimi'm','mu's'c'hle')
INSERT INTO table (field1, field2,field3,field4)  VALUES ('bla,bla','blub','mimi\'m','mu\'s\'c\'hle')
UPDATE table SET field1 =  'bla,bla', field2 = 'blub', field3 = 'mimi'm', field4 = 'mu's'c'hle' WHERE field5 = 'lol'zj'd'
UPDATE table SET field1 ='bla,bla',  field2 ='blub',  field3 ='mimi\'m',  field4 ='mu\'s\'c\'hle'  WHERE  field5 ='lol\'zj\'d'

With a little bit of effort, and correct reg_exp instead of simple explode/implodes and the proper escape function for your needs you can build a function that is capable of cleaning given querys

Upvotes: 2

leftclickben
leftclickben

Reputation: 4614

I believe this is insoluble with traditional means, at the time when the query is already built:

  1. Trying to simply add a slash to every apostrophe, of course will not work, because you are escaping the delimiter apostrophes as well as the "in-value" apostrophes.
  2. There is no function or regular expression to determine which are the in-value apostrophes and which are the value-delimiting apostrophes.
  3. Even a parser won't help you because part of a parser's job is to tell you when the query has invalid syntax, but not to fix that syntax; as soon as it hits an apostrophe out of place and the following character is not a comma, it will baulk.

Let's take part of your second example:

field3 = 'mimi'm', field4 = 'mu's'c'hle'

A normal query parser would see the field3 value as 'mini' followed by an erroneous m, where it expects a comma. This is not something a parser is designed to handle.

So suppose we write something custom to handle this. Let's say we decide that the apostrophe, given that it isn't followed by a comma, must be part of the value. That's fine, but what about the next apostrophe, which is intended to be a delimiter?

How does our code know whether the apostrophe is a delimiter, as opposed to the value actually containing an apostrophe followed by a comma? In fact, the value could contain something that looks exactly like the rest of the query! (Furthermore, how would we detect queries that actually are invalid, once we start to question the structure of the query itself in this way).

tl;dr

GIGO = garbage in, garbage out

You can't write (traditional) software to sort out an arbitrary mess!

Upvotes: 2

itsid
itsid

Reputation: 811

$query = "INSERT INTO table(field) Values('".addslashes("'")."')";

I think that's failsave or, even better 

$query = sprintf("INSERT INTO table(field) Values('%s')", addslashes("'"));

because that's most liekely easier to read in case you want to extend the insert someday.

[edit] as far as I know it doesn't matter which flavor of sql you use if you only want to have the strings escaped, but just in case addslashes works just as well here. And yes... there are some specialised sql-escape functions in php,

And.. rereading the question.. just having the query-string, not having the initial values it's rather hard to properly escape everything.

Upvotes: -1

Michel Tom&#233;
Michel Tom&#233;

Reputation: 389

You can use just do a simple str_replace(). I.E. str_replace("'", "\'", $string); .

EDIT: You can also do 

$str = "INSERT INTO table(field1, field2) 
                        VALUES (
                                replace(" . $value . ", Chr(39), Chr(39) & Chr(39)), 
                                replace(" . $value . ", Chr(39), Chr(39) & Chr(39))
                               );";

As Chr(39) refers to '.

Upvotes: 0

Chris Christensen
Chris Christensen

Reputation: 418

Unless you want to write something that can interpret the query and determine where the error is at and then somehow determine what the proper fix is, there is no way.

Further, if you do this you still haven't fixed your bigger issue, which is sql injection.

Upvotes: -1

Heberfa
Heberfa

Reputation: 441

If I understood your problem it should work

$name = addslashes("mu's'c'hle");
$query = "INSERT INTO teste (teste) VALUES ('$name')";

Upvotes: -1

Related Questions