Making multiple replacements using str_replace in php

Question

OK here's the problem:

I'm trying to write the query function for a database class in my project and I want to make it easier to escape the sql and check if it is harmful to the database in anyway.

Let's say I have a query like this:

INSERT INTO users (id,name,email,username,birthdate)
VALUES(1,'Josh','[email protected]','josh101','1978-11-02')

But it won't really help If I hardcode this into the function. So lets say I used a question mark for all the values I want to insert and then pass an array to the function containing the actual values I want to replace, just like the way it's done in codeigniter.

Here's a sample:

//Here's the way the function appears in the class definition.
    public function query($sql,$params=array()){

     if (!empty($params) && is_string($sql)):
               //do some stuff here.


            elseif (empty($params) && is_string($sql)):
                //do some other stuff here.

            else:
                //bad sql argument.
                die("Mysql_database ERROR: The query submitted is not a string!");
            endif;

    }

//Here's where the function is applied.
 $sql="INSERT INTO users (id,name,email,username,birthdate)
        VALUES(?,?,?,?,?)";

$params=array(1,'Josh','[email protected]','josh101','1978-11-02');

$db= new Mysql_database();

$response=$db->query($sql,$params);

Now here's what I want to do:

If the second argument is not provided, just run the query as it is.
Else check the elements of the array provided for their type and escape them properly then replace them in their appropriate positions in the pseudo-sql string provided.

The problem is that it seems that all the question marks get replaced by only the first element of the array:

Here's the code:

/*assuming I already have a function called create_array that well,
basically creates an array with n elements 
specified in the first parameter and fills each element with the value provided in 
the second parameter.*/
    $toreplace = create_array(substr_count($sql, "?"),"?");
    $sqlComplete = str_replace($toreplace, $params, $sql);

If I echo $sqlComplete I get this:

INSERT INTO users (id,name,email,username,birthdate)
VALUES(1,1,1,1,1)

What can I do so that each element of $params is put in its appropriate position in the sql string?

PS: Please don't tell me to just use codeigniter because I'm trying to challenge myself here a bit by building a project from scratch, I don't want to always depend on frameworks to get the job done.

bluish · Accepted Answer · 2012-11-27 09:41:52Z

2

Maybe just use MySQL prepared statements?

edited Nov 27, 2012 at 9:41

bluish

27.5k28 gold badges126 silver badges185 bronze badges

answered Nov 27, 2012 at 9:24

Nemutaisama

6113 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ninjacoder Over a year ago

Thanks, I didn't know anything about prepared statements, I think knowing how to use them would help a lot so I'll definitely read that. :)

MrCode · Accepted Answer · 2012-11-27 09:25:35Z

1

It can be done like this:

$params=array(1,'Josh','[email protected]','josh101','1978-11-02');

$sql="INSERT INTO users (id,name,email,username,birthdate)
    VALUES(?,?,?,?,?)";

foreach($params as $param)
{
    $pos = strpos($sql, '?');

    if($pos !== false)
    {
        $sql = substr_replace($sql,"'" . $param . "'",$pos,1);
    }
}

echo $sql;

Outputs

INSERT INTO users (id,name,email,username,birthdate) VALUES('1','Josh','[email protected]','josh101','1978-11-02')

This doesn't do any escaping, it just populates the values in the query. You'll need to add the escaping that's appropriate to the framework/DB API.

answered Nov 27, 2012 at 9:25

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

Collectives™ on Stack Overflow

Making multiple replacements using str_replace in php

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related