Select... From... Where with Ajax

Question

I'm trying to build a custom query from Ajax to PHP/MySQL considering the following.

Javascript code:

var i=2;
fetchFromDBPHP("name", "tblperson", "id="+i);    

function fetchFromDBPHP(column, table, condition) {
    $.ajax({
        type: "post",
        url: "./php/fetchFromDB.php",
        dataType: 'json',
        data: { column: column, table: table, condition: condition },
        success: function(data) {
            console.log("Fetched data from PHP:" + data);
        },
        error:function(request, status, error) {
            console.log("Reading PHP database went wrong. Error " + request.responseText);
        }
    });
}

PHP code:

<?php
    $column = $_POST['column'];
    $table = $_POST['table'];
    $condition = $_POST['condition'];

    if (isset($table)) {
        $sql = "SELECT " . intval($_POST['column']) . " FROM " . intval($_POST['table']) . " WHERE " . intval($_POST['condition']);
        $con = mysqli_connect("localhost", "root", "bloh", "blah");
        if (!$con) {
            die("Connection failed: " . mysqli_error($con));
        }
        $result = mysqli_query($con, $sql);
        $to_encode = array();
        while($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
            $to_encode[] = $row;
        }
        echo json_encode($to_encode);
        mysqli_close($con);
    }
?>

My table may be similar as that:

Table tblperson:

id  name    firstname   tel
1   Dalton  Jack        555-5555
2   Smith   John        555-6666
3   Doe     John        555-7777

What I would like to do is to send a query similar to

SELECT something FROM mytable WHERE condition=that

with Ajax. I have an error message, and nothing is retrieved.

I tried without, but I still have my error message. I agree, it's not needed. — EricF, Sep 13 '17 at 12:14
this is network tab in browser press F12 to open : http://infoheap.com/wp-content/uploads/2016/02/chrome-developer-tools-network-tab.png — Niklesh Raut, Sep 13 '17 at 12:24
I just went there, but I'm not sure what to see. I have no error here. — EricF, Sep 13 '17 at 12:43
May be you are hitting wrong url `url: "/php/fetchFromDB.php",` or it should be `url: "../php/fetchFromDB.php",` ? check — Niklesh Raut, Sep 13 '17 at 12:47

Niklesh Raut · Answer 1 · 2017-09-13T13:04:12.010

0

Remove intVal It makes words to 0 : check this https://eval.in/860766

    $column = $_POST["column"]?:"";
    $table = $_POST["table"]?:"";
    $condition = $_POST["condition"]?:"";
    if($column && $table && $condition){
      $sql = "SELECT `$column` FROM `$table` WHERE $condition";
    }else{
      die("Something went wrong.");
    }

Also be sure about string type value in condition, Lets say for column address

fetchFromDBPHP("name", "tblperson", "id='"+address+"'");

Use quotes ' so that it would be right query like Or Make a condition in php like this

$condition = explode("=",$condition);
if(!is_numeric($condition[1])){
 $condition[1] = "'".$condition[1]."'"; 
}
$condition  = implode("=",$condition);

Demo : https://eval.in/860838 , to make query like this

SELECT something FROM mytable WHERE condition='that'

You can use above code as first step, don't use in production, make another step to make it parameterized or use pdo.

edited Sep 13 '17 at 13:04

answered Sep 13 '17 at 12:17

Niklesh Raut

34,013
16
75
109

**Danger! DO NOT USE THIS CODE!**: It is **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that you need to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. – Quentin Sep 13 '17 at 12:35
@Quentin : I am completely agree with you. I have updated and put a note at last to use `parameterized` or `pdo`. Or should I remove this answer ? – Niklesh Raut Sep 13 '17 at 12:44
1

If the answer is "You're using a form of SQL injection defence that is designed for use specifically when the input should be a number but your input is not a number" then I'd suggest either following through and showing the right way to do the protection or voting to close a duplicate of the reference SQL injection question and making a comment about the problem being that the wrong method was selected. – Quentin Sep 13 '17 at 12:48

score 0 · Answer 2 · answered Sep 13 '17 at 13:03

0

Thanks to "user2486", my code was wrong. The right use is:

fetchFromDBPHP("name", "tblperson", "id='"+address+"'");

But, I also agree with "Quentin", the rest of his code is vulnerable to SQL injection attacks.

answered Sep 13 '17 at 13:03

EricF

181
1
3
19

Select... From... Where with Ajax

2 Answers2