string searching problem

[Wolxhound90]

Hey guys,

I have a problem with searching for a string within a string. Here's the code I have at the moment:

$query = mysql_query("SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = '".$db."'") or die(mysql_error());

while($table = mysql_fetch_array($query)){
$checktbl = $table['TABLE_NAME'];
foreach($_POST['fields'] as $f){
$f = substr($f,strpos($f, '.'));
if($checktbl == $f){
$from .= $f.", ";
}}}

$from = substr($from,0,-2);

This should take each table name (gotten from the $query string) and then compare it against $_POST['fields'] which is sent from the previous page. $_POST['fields'] has a string that is made of "tablename"."fieldname". Currently it comes up with an error saying Unknown table 'test' in field list.

Looking at the code now I'm not even sure if it will work how I want it to. For each table name I want to check if it exists in any of the $_POST['fields']. If it does then it will add the table name to the $from string, but only once, otherwise it will move onto the next table name.

Thanks in advance for any help :)

[diafol]

Ok, how about this?

foreach($_POST['fields'] as $f){
    $field_r = explode('.',$f);
    if(isset($field_r[1]){
        $from[] = $field_r[1];
    }
}
$from_string = implode(',',$from);

I would do a preg_match though to ensure table.field entity is valid - as you are blindly placing this data into a string to insert into an SQL query - it is v. unsafe. The elements should be escaped and possibly backticked. In addition, if you have a list of all tables and fields in an array - check the input data against this array using in_array(). The reason I suggest this is that a single table or field spelled incorrectly will cause the SQL query to fail.

[Wolxhound90]

Ok, I changed it to this to get rid of some errors that kept coming up:

foreach($_POST['fields'] as $f){
    $field_r = explode('.',$f);
    if(isset($field_r[1])){
        $from .= $field_r[1].", ";
    }
}
$from_string = implode(',',$from);

but this still doesn't work. It seems to be getting the field name rather than the table name from $_POST['fields']. It comes up with this error:

"Warning: implode(): Invalid arguments passed in D:\shares\fypshare\N0218430\querycreation.php on line 40 SELECT properties.Property_ID, properties.Property_Line1, properties.Property_Line2 FROM Property_ID, Property_Line1, Property_Line2Table 'm_fyp2011_n0218430.property_id' doesn't exist"

I'm not sure why it has made m_fyp2011_n0218430.property_id. m_fyp2011_n0218430 is the name of the database being accessed.

Thanks for your reply :)

[diafol]

Sorry forgot the$checktbland misplaced the $from_string:

    while(...){
        foreach($_POST['fields'] as $f){
            $field_r = explode('.',$f);
            if(isset($field_r[1]) && $field_r[0] == $checktbl){
                $from[] = $field_r[1];
            }
        }
    }
    $from_string = implode(',',$from);

[Wolxhound90]

Ok, with a little bit of jigging around I've got it working, had to put in an integer variable to sort out a repeating issue, but it's all sorted out now :).

Thank you so much again for your help ardav, it's much appreciated :)

[diafol]

Hey, how about this - avoid multiple loops:

$link_id = mysql_connect('localhost', 'root', '');
//change 'yourDB'
mysql_select_db('yourDB', $link_id);

//just change 'yourDB' again
$q = mysql_query(
"SELECT TABLE_NAME AS t, COLUMN_NAME AS c
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME IN
  (SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='yourDB') ORDER BY TABLE_NAME, COLUMN_NAME");

while($data = mysql_fetch_assoc($q)){
    $db_array[$data['t']][] = $data['c']; 
}

//print_r($db_array);
//clean your inputs
$formdata = array_map ('mysql_real_escape_string', $_POST['fields']);
foreach($formdata as $f){
    $fsplit = explode(".",$f);
    if(isset($fsplit[1]) && isset($db_array[$fsplit[0]]) && in_array($fsplit[1],$db_array[$fsplit[0]])){
        $fieldarray[] = $fsplit[1]; 
    }
}
$fields = implode(", ",$fieldarray);

//echo $fields;

It has the advantage of having the $db_array - you can build form widgets with this (e.g. dropdowns) if your form and php (this code) is on the same page.
You could turn that array into a json to make a dynamic field dropdown when you select a table from a dropdown.