PDO query with dynamic column names

Question

i am upgrading one application from MySql to PDo, now the application is big so i don't want to write query every time, instead i am creating some insert, update, select etc. functions which accept dyanamic table name, with column and its value in array.

can any one sugest me how i can create this .

so far i have done is

$connection = new PDO("mysql:host=$host;dbname=$database;charset=utf8", "$user", "$password");

for select

$field = array("column1","column2");
$sql = "SELECT ".$fields." FROM ".$table." ".$whereSQL." ";

for inser

$col_val = array("column1"=>value, "column2"=>2);
$query = "insert into ".$table." (".$fields.") values (".$values.")";

$query = $connection->prepare($sql);
$query->execute();

i try to do all this but for an example in insert query i want to pass array as

$col_val = array("column1"=>value, "column2"=>2);

some code and function here which make PDO query easy and insert all column and value correctly.

i am also looking same way to perform Update query.

as you can see here tabel, column and value are totally dynamic which will be pass to function.

for this moment i am using all odd query with

$query = $connection->prepare($sql);
$query->execute();

Thank you in advance.

You're learning PDO, which is great, but don't forget the #1 reason to use that is **prepared statements**, something you're completely side-stepping here. If you haven't already read the documentation, [read an introduction to using PDO effectively](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). — tadman, Apr 12 '16 at 18:30
@tadman do youk now any site where i can learn this prepare statemant as well. — php-coder, Apr 12 '16 at 18:31
You could try clicking the link I put there, or maybe reading the part in the comment about **reading the documentation**. Please, this is not hard. — tadman, Apr 12 '16 at 18:32

dquinonez · Answer 1 · 2016-04-12T19:42:05.757

-3

This is not a complete solution but that's the idea I think you could use to get closer to fix your issue.

$columns = array('column1', 'column2', 'column3')

$comma_separated = implode(",", $columns);

$columns_values = array(
    'column1' => 'text1',
    'column2' => 'text2',
    'column3' => 'text3',
    )

$values_query = "";
$index = 0

foreach ($columns as $column_name) {

    if ($index == 0){
        $values_query .= "'". $columns_values[$column_name]."'"
    }else{
        $values_query .= ", '". $columns_values[$column_name]
    }

}


$query = "INSERT INTO table (". $comma_separated . ") VALUES (".$values_query.");";

Before executing the query you can use PDO to escape the string ($query) to avoid SQL injection

edited Apr 12 '16 at 19:42

answered Apr 12 '16 at 18:33

dquinonez

123
7

That's not going to properly escape column names, so it's inadequate for this task. – tadman Apr 12 '16 at 18:33
ya i used this but it solve only column but what about value and as tadman suggest about preparing statement. – php-coder Apr 12 '16 at 18:35
This is getting better, but still doesn't use prepared statements, so those values are being injected as-is, which is a huge problem if you have a value like `O'Malley`. Likewise, the array of columns needs to be escaped so that a column name like `order` doesn't make the statement invalid. – tadman Apr 12 '16 at 19:47
@php-coder, when you get to the other issue where you want to add O'Malley here is a good question & answer about it http://stackoverflow.com/questions/1742066/why-is-pdo-better-for-escaping-mysql-queries-querystrings-than-mysql-real-escape – dquinonez Apr 12 '16 at 19:52
Next time I'm going to prepare a tutorial for each possible issue that the developer is going to encounter when I try to answer a question. What do you think Tadman? – dquinonez Apr 12 '16 at 19:54
@dquinonez I don't think that's necessary, but overlooking the two most serious problems when trying to deal with a problem like this is an issue. Sorry to be so harsh, but simple mistakes like this can have [disastrous consequences](http://codecurmudgeon.com/wp/sql-injection-hall-of-shame/). Column names and values absolutely must be escaped or your site will be compromised. There's no wiggle room here. – tadman Apr 13 '16 at 01:43

PDO query with dynamic column names

1 Answers1