#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    How to run sql query through html form passing to php to process?


    I am trying to build a HTML page where it there will be a <textarea> to type SQL statements
    like this:

    <html>
    <head>
    <script type="text/javascript" src="jquery.js"></script>
    <script type="text/javascript">
    function get() {
    $.post('toolProcess.php', {name: form.code.value},
    function(output) {
    $('#result') .html(output) .show() ;
    }
    );
    }
    </script>
    </head>
    <body>
    <p>
    <form name="form">
    Run SQL query/queries:<br/>
    <textarea cols="80" rows="4" name="code"></textarea><br/>
    <input type="button" value="Submit" onClick="get();">
    </form>

    <div id="result"> </div>
    </p>

    and <Submit> button to process it through php file so that I can run query through form to php to connect to mySQL database to either execute the result for SELECT statement or INSERT statement.
    I am having problem with creating php to do this task. Any suggestions?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    721
    Rep Power
    7
    Well, this site offers a basic layout of a php file holding database login info, making the connection, submitting a query, and tossing a simple echo of what it finds. I'd work with this for a bit to understand what it is doing. Once you feel comfortable, the next step will be passing the lines from your form to this file for processing, and having the php return its action or so, as desired. Keep in mind there are many ways security can become a risk. Once you understand how to pass and proccess the items, focus on placing security whenever possible.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    the question is: Why do you want people to enter SQL queries? That's pretty much the worst project for a beginner, because if you make any mistake regarding security, you can say good bye to your server (the whole server, not just your database).

    Letting people execute SQL queries is extremely critical and requires solid knowledge of both PHP and database administration. And it hardly ever makes sense. Why not display specific database tables as HTML tables and allow your users to insert rows through a form?

    So you should really consider using a different approach.

    Apart from that, I'm not sure how we could help you reading up on the basics. Just open the PHP manual and work your way through the database chapter.

    Be aware that many of the PHP database "tutorials" out there are obsolete and/or use unsecure practices. So it's usually best to learn directly from the source.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    re:


    Actually this is part of my assigment that i need to build. Yes I do understand the nature of risk that it may possess. But, this is the matter of knowing how to manupulate the form through php and retrieving data from mySQL.
    LIke i said before, this is absolutely for my class project and will be in the internal server of my college only.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by n3pl3x27
    LIke i said before, this is absolutely for my class project and will be in the internal server of my college only.
    Well, apart the security considerations: simply running the raw user input through the SQL interpreter also isn't a very advanced way of applying PHP/SQL knowledge.

    I don't know how important this assignment is for you and how much you (and your teacher) care, but I'd choose a more realistic and ... intelligent approach. Maybe a guestbook or registration form or something like that.
  10. #6
  11. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Originally Posted by n3pl3x27
    Actually this is part of my assigment that i need to build. Yes I do understand the nature of risk that it may possess. But, this is the matter of knowing how to manupulate the form through php and retrieving data from mySQL.
    LIke i said before, this is absolutely for my class project and will be in the internal server of my college only.
    Well if you and your teacher dont give two craps about security use the following at your own risk
    PHP Code:
    /**
     * @desc    Most insecure app ever (PLease don't use it)
     */

    // database vars
    $db     'db1';
    $usr    'root'// maximizing the potential mayhem ;D
    $pwd    '';
    $host   'localhost';


    if(isset(
    $_POST['submit']) && !empty($_POST['query'])){
            
    //
            
    $mysqli = new mysqli($host$usr$pwd$db);
            if (
    $mysqli->connect_errno) {
            echo 
    'Failed to connect to MySQL: (' $mysqli->connect_errno ')' $mysqli->connect_error
        }
        
    // proceed to screw this database up
        
    if(!$mysqli->query($_POST['query'])){// always trust your users... :P
            
    echo 'something went wrong executing ('.$mysqli->errno.''.$mysqli->error.')';
        }else{
            echo 
    'you succesfully executed: '.$_POST['query'];
        }
    }

    ?>

    <form action="" method="post">
        <div>
            <textarea name="query"></textarea>
            <input type="submit" name="submit" value="submit" />
        </div>
    </form> 

    Comments on this post

    • n3pl3x27 agrees
    Last edited by aeternus; December 19th, 2012 at 05:35 AM.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0

    Smile


    Finally got my solution: Thank you for your help
    PHP Code:

    <form action="" method="post"> 
        <div> 
            <textarea name="query"></textarea> 
            <input type="submit" name="submit" value="submit" /> 
        </div> 
    </form>
    <div>
        
    <?php
    if(isset($_POST['submit']) && !empty($_POST['query'])){ 
            
    // 
            
    $mysqli = new mysqli("hostname""username""passwd""database name");
        
    //set up character set
        
    $mysqli ->query("SET NAMES 'utf8' ");
        
            if (
    $mysqli->connect_errno) { 
            echo 
    'Failed to connect to MySQL: (' $mysqli->connect_errno ')' $mysqli->connect_error;  
        }
        
        if(!
    $mysqli->query($_POST['query'])){// always trust your users... :P 
            
    echo 'something went wrong executing ('.$mysqli->errno.''.$mysqli->error.')'
        }else{ 
            echo 
    'you succesfully executed: '.$_POST['query']; 
        
        
    /*******************/
    //$result = $_POST['query']->use_result();
    ($result $mysqli->query($_POST['query'])); 
    if (
    $result === FALSE)
      {
        
    $errno $mysqli->errno;
        
    $errmsg $mysqli->error;
        echo 
    "Connect Failed with: ($errno$errmsg<br/>\n";
        
    $mysqli->close();
        exit;
      }
    else {
          echo <<<EOM
        <table border='1'>
        <tr>
          <td>Branch No</td>
          <td>Street</td>
          <td>City</td>
          <td>Postcode</td>
        </tr>
    EOM;

        while ((
    $row $result->fetch_assoc()) !== NULL)
        {
          echo <<<EOM
      <tr>
        <td>
    {$row["branchNo"]}</td>
        <td>
    {$row["street"]}</td>
        <td>
    {$row["city"]}</td>
        <td>
    {$row["postcode"]}</td>
      </tr>

    EOM;
        }
        echo <<<EOTABLE
      </table>
    EOTABLE;
     
    //
        // clean up resultsets when we're done with them!
        //
        
    $result->close();
    }
        
    /*******************/
        


    ?>

    </div>

IMN logo majestic logo threadwatch logo seochat tools logo