generating sql from postdata

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

generating sql from postdata

Post 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:)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

and ...do you have a (specific) question?
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post 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?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

ummmmmmmmmmmmmmmmmmm...........................

http://php.net/manual
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Maybe

Code: Select all

<?php
foreach($_POST as $key=>$value) {
	echo $key, '=>', $value, " <br />\n";
}
?>
gets you a starter.
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Re: re

Post 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.
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post 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)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Take a look at the user comments and/or the PEAR::Compat library.
Post Reply