4 replies to this topic

[Teonnyn]

I am writing a management script that goes over a number of queries and organizes them by runtime, with input options via form or
already-existing query storage tables.

However, on my form I'd like to double-check that the string entered is actually an SQL statement - is there any way to do that,
IE.. prevent simple strings from getting input into the database. What is the best way to do that?

[RHochstenbach]

If your database tables are in the innoDB format, you can do the following:

// Put the query in the variable

$query = $_POST['query'];


// Execute the query, store the result in a variable and then undo it.

mysql_query("START TRANSACTION;");

$check = mysql_query($query);

mysql_query("ROLLBACK;");


// If the query was successful, repeat it for real this time

if($check === true) {

    mysql_query($query);

  } else {

    echo "Not a valid query!";

}

The above is a theory though, so give it a try and please come back with the results :)

[Orjan]

If you want to use transactions like that, you might need to turn autocommit off to be able to roll back properly.

[RHochstenbach]

Orjan said:

If you want to use transactions like that, you might need to turn autocommit off to be able to roll back properly.

Thanks for the advice :)

In that case it would be something like this:

mysql_query("SET autocommit = 0;");

// Put the query in the variable 

$query = $_POST['query']; 


// Execute the query, store the result in a variable and then undo it. 

mysql_query("START TRANSACTION;"); 

$check = mysql_query($query); 

mysql_query("ROLLBACK;"); 


// If the query was successful, repeat it for real this time 

if($check === true) { 

    mysql_query($query); 

  } else { 

    echo "Not a valid query!"; 

}  

[Orjan]

But, on the other hand, such a function is so vulnerable. I would probably let a user build their SQL statemens with dropboxes and stuff so they can't do any harm. with that, someone could easily do a drop database command, and then ... well, I think you get the point.