how to perform sql command on html page with user input field, and show result on the same page

Question

i write a command, or i fill up parameter value from user input field. click the button, send this command to php and send resultant value back to html to display. for example. on html page :

select ___ from ____,

two available input field i fill up with "tablenameone" and "valueone". then, result will be printed on html text field on the same page.

what i do know is those value can be sent(perhaps) as in such format

$('input[name="talbename"]') 
$('input[name="value"]')
example.com?tablename=tablenameone&value=valueone

and from php side i use

 $sql="SELECT '$_GET['value']' FROM '$_GET['tablename']';

what i dont know is that....how exactly should i perform this in a click function? its for sure using ajax. but how can i produce example.com?tablename=tablenameone&value=valueone and where should i put $('input[name="value"]')

thanks in advance :D

So you're letting the user decide which field select, where to do that, and without sanitizing? I don't know how long your db will last — Damien Pirsy
– Damien Pirsy, Commented Oct 17, 2011 at 15:23
guys..lol good joke from ur link marc B....i just made my question simplified so i can start from basic....i actually planned to use selection instead... — user987013
– user987013, Commented Oct 17, 2011 at 15:26
stackoverflow.com/questions/7794055/… here is my original question...took me some time to explain my actual problem.. — user987013
– user987013, Commented Oct 17, 2011 at 15:27

Fenton · Accepted Answer · 2011-10-17 15:30:46Z

You must not use direct input in your queries as you will be open to SQL injection attacks.

$sql="SELECT '$_GET['value']' FROM '$_GET['tablename']';

Instead, use the following:

$column = $_GET['value'];
$table = $_GET['tablename'];
$sql = sprintf("SELECT %s FROM %s;",
             mysql_real_escape_string($column),
             mysql_real_escape_string($table));

Although you are still exposing too much "inside information" by giving people a page that tells them all of your table and column names!

Anyway, here is a complete example;

<form method="post" action="">
    <fieldset>
        <legend>Select Data</legend>
        <p><label>Table<br>
        <select name="table">
            <option value="tblStudents">Students</option>
        </select></label></p>
        <p><label>Table<br>
        <select name="column">
            <option value="firstname">First Name</option>
            <option value="lastname">Last Name</option>
        </select></label></p>
        <p><input type="submit" name="submit" value="submit">
    </fieldset>
</form>
<?php
$connection = mysql_connect("servername:3306", "user", "password") or die ('Error connecting to mysql');

mysql_select_db("databasename");  

$column = mysql_real_escape_string($_POST['column']);
$table =  mysql_real_escape_string($_POST['table']);
$sql = sprintf("SELECT %s FROM %s;",
        $column,
        $table);

$result = mysql_query($sql) or die(mysql_error());

echo '<ul>';
while($row = mysql_fetch_array($result)) { 
    echo '<li>' . $row[$column] . '</li>';
}
echo '</ul>';

mysql_close($connection); 
?>

rickyduck · Accepted Answer · 2011-10-17 15:32:41Z

1

Seeming as though noone has actually answered the question (although they are all good points, I will assume there is a reason for you doing this), I will answer:

$('form[name=formname]').submit(function(e){
    e.preventDefault;
    var tablename = $('input[name="tablename"]').val();
    var value = $('input[name="value"]').val();
    $.get("example.php?tablename="+tablename+"&value="+value, function(data){
         $('body div').text(data);
    })
});

PHP:

$sql=mysql_query("SELECT '$_GET['value']' FROM '$_GET['tablename']'")or die(mysql_error());
$sqlOutput = mysql_fetch_array($sql);
echo "<pre>";
print_r($sqlOutput);
echo "</pre>";

Obviously replace formname with your form name, body div with the name of the element you want the output to go in and all other identifiers replaced where seen fit. Then change the output in the PHP to suit your needs.

Again, do bear in mind the posts regarding SQLi, because you have yourself a very serious problem there.

answered Oct 17, 2011 at 15:32

rickyduck

4,08414 gold badges62 silver badges95 bronze badges

2 Comments

user987013 Over a year ago

can i use $("input.classname").click(function (e) { instead? preventDefault is for not going to the php page isnt it? :D

rickyduck Over a year ago

sure, however if the e.preventDefault on the submit it will work fine as it prevents the form from actually submitting and then handles all the AJAX

Community · Accepted Answer · 2017-05-23 12:03:36Z

0

You really want to make sure you are not open to SQL injection.

You could use mysql prepared statements

or

use the php function mysql_real_escape_string($_GET['value'])

Read this thread: How can I prevent SQL injection in PHP?

I'm not sure what you mean by the click function.

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Oct 17, 2011 at 15:24

Derek Organ

8,49317 gold badges60 silver badges75 bronze badges

Collectives™ on Stack Overflow

how to perform sql command on html page with user input field, and show result on the same page

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related