Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

PHP > SQL checking class

php

  • Please log in to reply
4 replies to this topic

#1 Teonnyn

Teonnyn

    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 12 February 2012 - 06:31 PM

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?
  • 0

#2 RHochstenbach

RHochstenbach

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 56 posts

Posted 22 February 2012 - 10:41 AM

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 :)
  • 0

#3 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 22 February 2012 - 03:31 PM

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

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#4 RHochstenbach

RHochstenbach

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 56 posts

Posted 23 February 2012 - 12:38 AM

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!";
}

  • 0

#5 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 24 February 2012 - 08:01 AM

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.
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.






Also tagged with one or more of these keywords: php

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download