CODEWITHSUNDEEP
phpjavascriptjquerymysqlpost
MaVe
MaVe

Reputation: 1725

Passing MySQL query via Javascript

In a Javascript function, I have the following JQuery in which I call a PHP script (i.e. getDBData.php) to get the database data from the query:

$("#dbcontent").load("getDBData.php", {query: "SELECT * FROM `texts` WHERE name='John' LIMIT 10;"});

In getDBData, I fetch this query via POST:

$query = $_POST['query'];

and give it as input for mysql_query:

$query = mysql_query($query) or die(mysql_error());

However, I get the following MySQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'John\' LIMIT 10' at line 1

What could be wrong here? I guess it has something to do with character encoding when passing the query, but don't seem to get it right.

Upvotes: 1

Views: 2927

Answers (3)

11684
11684

Reputation: 7507

Don't do it this way, as others have pointed out! Webnet's solution is much better!

But I believe I know how to solve your initial problem. I had it once too, when parsing JSON, and solved it by using stripslashes().

$query = stripslashes($_POST['query']);
mysql_query($query);

Upvotes: 1

Ben
Ben

Reputation: 62356

You should never do this under any circumstances. You should be passing parameters that can then be used to build the proper query.

At least do something like this....

Javascript

$.post('getDBData.php', {
   query: 'getTextsByUser',
   user: 'John'
});

PHP

$queries = array(
   'getTextsByUser' => 'SELECT * FROM texts WHERE name = ?',
   'getNewsById' => 'SELECT * FROM news WHERE id = ?'
);
$stmt = $dbConnection->prepare($queries[$_POST['query']);
$stmt->bind_param('s', $_POST['user']);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

And then pass getUsers via ajax to determine which query to run.

Note: If you're just beginning this project, mysql_query() has been deprecated and you should consider switching to mysqli.

Upvotes: 5

joedborg
joedborg

Reputation: 18343

As many people have stated, this is very risky. I think what's failing, however, is the fact you need to specify the query with URL encoding. See http://en.wikipedia.org/wiki/Query_string#URL_encoding. This especially applies to the = symbol.

Upvotes: 0

Related Questions