Help in displaying data from Database

Question

I have created a SQL page wherein users can run SQL queries. My problem in that I dont know what columns the user will input. Hence I cant display what he has asked for. Like if his query was : select id from emp; How do i know what column name he has input and hence display it in the results page. Here is the exact query which you can use and help me out. I have used numerous IF cases in my code. Eg: if(strpos($query,"id")) then it displays the id column. Please help me. And thanks in advance !

I would love to see the queries history to see the various attacks — Benjamin Crouzier
– Benjamin Crouzier, Commented Aug 23, 2011 at 17:34
Normally, you should be able to get the list of column headings from the query result, using mysql_num_fields and mysql_field_name functions. — Aleks G
– Aleks G, Commented Aug 23, 2011 at 17:37
@pinouchon my first idea was to type 'drop database x' into the field :) On the other hand, he may actually be parsing the entered query to only allow certain things. — Aleks G
– Aleks G, Commented Aug 23, 2011 at 17:38
@Aleks G when i see this kind of website, i just can't resist — Benjamin Crouzier
– Benjamin Crouzier, Commented Aug 23, 2011 at 17:41

GolezTrol · Accepted Answer · 2011-08-23 17:36:20Z

1

You must be kidding, letting users write these kind of statements in a webform that the whole world can access..

That said, when you execute a query, you can fetch the result row by row into a key=>value array. The keys will contain the field names. Use mysql_fetch_array for this.

But really. If you got your code working, I can write

SHOW DATABASES;
DROP DATABASE <databasename that is on my screen>

And suddenly you got an empty server. Get this script offline as soon as you can.

answered Aug 23, 2011 at 17:36

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

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

1 Comment

Atulmaharaj Over a year ago

This was rather is just and experiment. No useful data is available to the users. Also not all rights are given to the user... SQL is a part of our course and thought to try out...

score 1 · Accepted Answer · 2011-08-23 17:53:02Z

1

I go with GolezTrol, this isn't save at all.

You could of course check with if-statements if the user has used the words 'DROP' or 'SHOW', or just only allow certain things, but I don't know if that is save...

edited Aug 23, 2011 at 17:53

answered Aug 23, 2011 at 17:40

user742860

Comments

Jarrod Sears · Accepted Answer · 2011-08-23 18:55:48Z

1

It is safe as long as he limits the sql user's permissions assosiated with the php mysql call. Only give it select permissions for that one database and it really doesnt matter if it is on a public website (assuming you dont care that everyone can see the data).

This will format the sql output into a decent looking table for you:

$header = null;
$colspan = 0;
while($values = odbc_fetch_array($rs))
{
    $result .= "<TR>";
    if(is_null($header))
    {
        foreach(array_keys($values) as $key)
        {
            $header .= "<TH style='border:1px dashed #00FF00;padding:5px 5px 5px 5px;align:left;'>$key</TH>";
            $colspan++;
        }
    }

    foreach($values as $value)
    {
        $result .= "<TD style='border:1px dashed #00FF00;padding:5px 5px 5px 5px;align:left;'>$value</TD>";
    }
    $result .= "</TR>";
}
}
$result = "<TABLE style='padding: 1px 1px 1px 1px;border:dash 1px #00FF00;color:#00FF00;'>".
          "<tr><td colspan='$colspan'>>>" . $sql . "</tr></td>" .
          $header.$result."</TABLE>";

answered Aug 23, 2011 at 18:55

Jarrod Sears

1,1303 gold badges17 silver badges33 bronze badges

2 Comments

Atulmaharaj Over a year ago

I can get the data to be displayed in a tabular form. The problem I am facing is how do I know what column the user has enter and thus display it. Thanks for it btw. :)

Jarrod Sears Over a year ago

That is what the header part of the above code is for. It takes the keys for the array and uses them as column names.

Charles R · Accepted Answer · 2011-08-23 17:42:08Z

0

mysql_fetch_field can grab column names from a result set, along with lots of other information about the column (type, max_length, etc.) that might be useful to display in a program like this.

answered Aug 23, 2011 at 17:42

Charles R

4413 silver badges4 bronze badges

Comments

Jasper · Accepted Answer · 2011-08-25 15:49:30Z

0

When you run the output from the database query do something like this:

$query = mysql_query("YOUR_USERS_QUERY_HERE");

if (mysql_affected_rows() > 0) {
    while ($row = mysql_fetch_assoc($query)) {
        $keys = array_keys($row);
        foreach ($keys as $a_key) {
            echo $a_key . "=" . $row[$a_key] . " <br /> ";
        }
        echo "<br />";
    }
} else {
    echo "No Rows Found";
}

-- EDIT --

To output the header once you can do something like this:

$first = true;
while ($row = mysql_fetch_assoc($query)) {
    if ($first == true) {
        //output header code here
        $first = false;
    }
    //output data row code here
}

edited Aug 25, 2011 at 15:49

answered Aug 23, 2011 at 17:37

Jasper

76.1k14 gold badges153 silver badges148 bronze badges

2 Comments

Atulmaharaj Over a year ago

Hey, According to the above post, I did get what i wanted. Thanks. But can you help me with a little bit of formatting ?? Please visit link give a query - select * from emp; Please see the output and tell me where the problem is..Thanks in advance... :)

Jasper Over a year ago

It looks like you are outputting the header row every loop. Output the header row once.

Collectives™ on Stack Overflow

Help in displaying data from Database

5 Answers 5

1 Comment

Comments

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related