Page 1 of 1

generating sql from postdata

Posted: Thu Jan 11, 2007 2:06 pm
by yshaf13
hi, i want to create one universal processing page that will process forms with different fields, what i think is easiest is to analyze the POSTdata and based on that generate sql. (p.s. i'm a newbie so i need some pretty simple help:)

Posted: Thu Jan 11, 2007 2:10 pm
by volka
and ...do you have a (specific) question?

re

Posted: Thu Jan 11, 2007 2:13 pm
by yshaf13
yeah sorry i didn't clarify, how would i go about telling php to analyze the postdata and generate sql based on that?

Posted: Thu Jan 11, 2007 2:18 pm
by Kieran Huggins
ummmmmmmmmmmmmmmmmmm...........................

http://php.net/manual

Posted: Thu Jan 11, 2007 2:55 pm
by volka
Maybe

Code: Select all

<?php
foreach($_POST as $key=>$value) {
	echo $key, '=>', $value, " <br />\n";
}
?>
gets you a starter.

re

Posted: Thu Jan 11, 2007 6:23 pm
by yshaf13
heres what i came up with:

Code: Select all

<?php


$post=Array('id'=> 21, 'title' => 'one', 'short_desc' => 'two', 'other' => 'three' ) ;

$col='';
$val='';
$i=1;
 foreach($post as $key=>$value) {

         $i>1?$c=', ':$c='';
        $col=$col.$c.$key;
        $val=$val.$c.$value;
        $i++;
        }

         $sql="insert into tablename ($col) values ($val)"   ;
        echo $sql;
?>
comments?

Posted: Thu Jan 11, 2007 6:51 pm
by feyd

Posted: Fri Jan 12, 2007 5:44 am
by yshaf13

Code: Select all

$col2=implode(', ',array_keys($post));
$val2=implode(', ',array_values($post));
$sql2="insert into tablename ($col2) values ($val2)";
that works great! thank you feyd!
one more question though, what if i have other stuff in the post array before the values i want to use for exaple:

Code: Select all

$post=Array('misc'=> '25', 'table' => 'curr', 'bla' => 'blabla', 'id'=> '21', 'title' => 'one', 'short_desc' => 'two', 'other' => 'three' ) ;
lets say i only want from "id" and on what would i do?

Posted: Fri Jan 12, 2007 9:56 am
by feyd
There are many paths to that solution. I prefer to create a list of expected fields, iterate through the submitted data and filter out any that are not desired. I will add code that verifies the information in each expected field is the correct type of information as well.

re

Posted: Sat Jan 13, 2007 10:34 am
by yshaf13
what if i have no idea what the expected fields are but i do know that everything after "id" i want, is there any way i take everything from id and on?

Posted: Sat Jan 13, 2007 10:43 am
by feyd
A list must be provided from somewhere. The database table provides a list of possible fields. Blindly using submitted data without verifying that they are expected can lead to security compromises.

re

Posted: Sat Jan 13, 2007 5:45 pm
by yshaf13
first of all, how would i get that list from the db? and second, is there anyway to just do everything from id and on?

Re: re

Posted: Sat Jan 13, 2007 8:58 pm
by feyd
yshaf13 wrote:first of all, how would i get that list from the db?
DESCRIBE or SHOW CREATE TABLE queries.
yshaf13 wrote:and second, is there anyway to just do everything from id and on?
Yes.

array_keys() + array_search() + array_slice(); array_values() + <the array_search() result from before> + array_slice(); array_combine(). If you do a query blindly with the results your queries are very easy to attack. This is a major security hole.

re

Posted: Sun Jan 14, 2007 7:41 am
by yshaf13
wow thats really great but here's the problem: the server i'm using is running php 4.3 so array_combine dosen't work, is there any other functions i could use? (i'm using godaddys free hosting so i don't have too much control)

Posted: Sun Jan 14, 2007 9:30 am
by feyd
Take a look at the user comments and/or the PEAR::Compat library.