[RESOLVED] sql from a command line sort of?

chris_davies

Hi Folks,
I have started an assignment for college, where I have to create a form to view fields from a MySQL database using PHP - no problems there. The problem I have is another part of the assignment which requires me to be able to input a complete SQL command in via an input/textarea box. What the assignment has asked for is something similar to the SQL window as in PHPMyAdmin. What I need to know is how do I parse a complete string such as - SELECT * FROM table WHERE clause = CONDITION - I know it's easy enough embedding SQL into PHP, I don't have a problem with that at all, what I think I need to find out is how to parse all the redundant characters in the string, spaces etc.
Any pointers with this would be gratefully received.

cheers Chris

herve

Why do you want to parse the SQL query ?

chris_davies

Sorry if I'm not being very clear, I have to create an input box where a user enters a full sql statement, like in the sql window of phpmyadmin. what ever the user enters, be it a
simple -SELECT * FROM tableName - or even a complicated query, must return the correct results.

laserlight

That's easy. All you need to do is get the input from the user, then run the query with the input.

herve

You just need to execute the query, not analyze it ?

Something like this :

<?PHP

$db = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $db);

$query = $_POST["query"];

$result = mysql_query($query,$db);
$myrow = mysql_fetch_array($result);

if ($myrow)
{
	do
	{
		$reset($myrow); // Not really needed, but anyway

		while (list($key,$value) = each($myrow))
		{
			print "Column $key has value $value<br>\n";
		}

	}
	while ($myrow = mysql_fetch_array($result));
}

?>

chris_davies

not that simple surely? good grief I am almost ashamed of myself. I will try this immediately. Thanks guys you're the tops.

Chris

chris_davies

ok guys,
I got that working sort of, I am running a query against a table I know contains three records, but the data won't display although I am getting the three rows and a table. Also, depending on what query the user inputs, how do I get the correct amount of fields to display?

chris_davies

got all that working on simple stuff like select * from user, but as soon as you add criteria such as a where clause and condition, php and mysql throws it right out the window.

bradgrafelman

"Throws it right out the window." - Do you get an error message? What does the output look like? Have you tried echo()'ing the $query after they submit to make sure it's not being escaped or something?

Also, try adding some error debugging to your SQL commands, like so:

<?PHP 

$db = mysql_connect("localhost", "mysql_user", "mysql_password"); 
mysql_select_db("database", $db); 

$query = $_POST["query"];

echo "Query was: <b>$query</b><br>\n";

$result = mysql_query($query,$db) or die('MySQL Error: ' . mysql_error($db)); 

while ($myrow = mysql_fetch_array($result)) { 

	foreach($myrow as $key => $value)

		echo "Column $key has value $value<br>\n";
}

?>

EDIT: You know, perhaps using the CLI would be easier? Try commands like this:

if(get_magic_quotes_gpc())
    $query = $_POST['query'];
else
    $query = addslashes($_POST['query']);

$exec = exec('mysql --user=mysql_user --password=mysql_password --database=database --html --execute="' . $query . '"', $output);

echo $output;

chris_davies

Arrgh,
after getting all that to work, I have now been told that I am not allowed to use $POST or $GET, and that I am to make use of the SERVER VARIABLES, apparently my lecturer wants a CGI Script done in PHP. Now I really am stuck. any takers? how do I replace $_POST with a server variable?

bradgrafelman

Uhhh.... well you've got me puzzled. I've always called $_GET and such server variables... superglobals that are populated with data by the PHP engine.

chris_davies

yeah, that's what I thought, apparently I am supposed to use PHP's
$SERVER['QUERY_STRING] and PHP's $SERVER['REQUEST_METHOD'] variables which, on having used these variables, after posting the SQL string I get the following type of error ( well not an error but a problem I can't at the moment get around).

On printing the value of QUERY_STRING to the page ( which I do to track variables when I get a problem) I get this value:

SELECT+*+FROM+USER

So how do I strip out the Plus (+) characters? Before I was just using POST and used stripslashes(), which was, to be brutally honest, rather simple for a level three university course. I think the exercise is meant to create some sort of rudimentary parser.

Any takers?

Chris

Drakla

If it's coming in raw then run [man]urldecode[/man] on it.

The $POST array won't be set if you're running it as a true cgi script, but you can be a clever dick and bring it all back into place like so.

parse_str(file_get_contents('php://stdin'), $_POST);
$_REQUEST = array_merge($_REQUEST, $_POST);

chris_davies

I am actually sending/posting the sql string via $_SERVER['QUERY_STRING'] then assigning to $query on the action page, While coding I like to print my variables to the page so I can see what's going on and I get this:

SELECT+*+FROM+USER

I am not allowed to used $_POST in any shape or form or I fail the assignment.what I want to know is how to strip out the plus's out of the string.

cheers chris

Drakla

urldecode

chris_davies

Brilliant thanks , because I was using $_SERVER['QUERY_STRING'] the string being passed over was actually:
query=SELECT+*+FROM+USER

which included the query=, so after your suggestion I used:

$query = str_replace('query=','',urldecode($_SERVER['QUERY_STRING']));

which nailed it, thanks for your help. I like this forum 😮) I will be back

bradgrafelman

Glad to hear you've solved it! Don't forget to mark this thread resolved.

chris_davies

erm how do I mark this post as resolved? lolol