Passing javascript variable into PHP and making mysql query

Question

I've the following script (taken from a large script) saved as example3.php:

<script type="text/javascript">
var layer;

//where layer is a table like 
------------------------------------
BUSNAME       +    Category    +
------------------------------------
200 Bay       +   Restaurant   +
201 Bay       +   Bank         +
202 Bay       +   School       +
203 Bay       +   Restaurant   +
204 Bay       +   School       +
205 Bay       +   Restaurant   +
206 Bay       +   Restaurant   +
207 Bay       +   School       +
208 Bay       +   Restaurant   +
209 Bay       +   Restaurant   +
------------------------------------

window.location.href = "example3.php?layer="+ layer;

<?php
  //Make a MySQL Connection
  $query = "SELECT Category, COUNT(BUSNAME) 
    FROM ".$_GET['layer']." GROUP BY Category"; 
  $result = mysql_query($query) or die(mysql_error());
  //Print out result
  while($row = mysql_fetch_array($result)){
    echo "There are ".$row['COUNT(BUSNAME)']. " " .$row['Category']. "items.";
  echo "<br/>";
  }
?>

</script>

Don't no why it is not working. Any suggestion must be appreciated.

[SQL injection alert!](http://unixwiz.net/techtips/sql-injection.html) — BalusC, Apr 27 '11 at 21:30
@teresko and David, come on, don't just point it out, tell the man how to fix it! — Johan, Apr 27 '11 at 21:53

score 4 · Answer 1 · answered Apr 27 '11 at 22:29

4

if you have dynamical table name, your database design is wrong

as for your script, it just makes no sense.

make in in 2 parts: JS page and PHP page. and call this PHP page with window.location.href

answered Apr 27 '11 at 22:29

Your Common Sense

156,878
40
214
345

score 1 · Answer 2 · answered Apr 28 '11 at 09:10

1

I suggest JSON-encoding the table on the client side (in JS). Use POST to send it, then decode it on the server side (in PHP).

Most popular JS libraries do have JSON-encoding functions, the PHP function is called json_decode.

answered Apr 28 '11 at 09:10

vbence

20,084
9
69
118

score 0 · Answer 3 · answered Apr 27 '11 at 21:36

As Balus pointed out, watch out for SQL Injection. When you reach the php page, does the $_GET['layer'] equals something?

Does your javascript variable equals something? var layer = "layer

"layer" being the actual name for the table inside of your database.

score -1 · Answer 4 · edited May 23 '17 at 12:29

-1

EDIT

In this context mysql_real_escape_string() does not work.

$layer = mysql_real_escape_string($_GET['layer']);
$query = "SELECT Category, COUNT(BUSNAME) 
FROM `".$layer."` GROUP BY Category"; <<-- this doesn't work.

Because mysql_real_escape_string does not escape backticks `

With values it does work

You need to run each and every value you get with $_GET though mysql_real_escape_string(). (or use PDO's)
On top of that every value that you put into a query you need to enclose with single quotes '.

$value = mysql_real_escape_string($_GET['fieldname']);
$query = "SELECT * FROM test WHERE f1 = '".$value."'"; <<-- this works
                                        ^          ^

Links:
SQL-injection attack: How does the SQL injection from the "Bobby Tables" XKCD comic work?
PDO's: mysqli or PDO - what are the pros and cons?

Will update as soon as I find a fix your your particular SQL-injection problem.

edited May 23 '17 at 12:29

Community

1
1

answered Apr 27 '11 at 22:04

Johan

74,508
24
191
319

did you happen to use PDO yourself? – Your Common Sense Apr 27 '11 at 22:26
Yes, but I'm not a php man. I'm a delphi programmer, Delphi is strongly typed, so inserting integers I sometimes do as `query1.SQL.text:= 'SELECT a FROM b WHERE a = '+IntToStr(i);`. If I put strings in I always do `query1.ParamByName('x').AsString:= MyString;` – Johan Apr 27 '11 at 22:30
4

Comment is: - unhelpful - rude - incorrect - incomplete to name a few. – Johan Apr 27 '11 at 22:40
@Col and besides I'm answering the MySQL part, with a light sprinkling of php to glue things together. But if the post has factual errors, feel free to edit them out. – Johan Apr 27 '11 at 22:42
1

yeah. flagged the answer for it's extremely poor quality. should've been do it instead of commenting. apologies. – Your Common Sense Apr 27 '11 at 22:46
OK, that's cool, but what exactly **is** wrong with using `mysql_real_escape_string()` to prevent SQL-injection attacks? – Johan Apr 27 '11 at 22:58
@Pekka, thank you, @Col. Scrapnel I know you get upset at the poor quality of the php answers. Will edit the answer as soon as I find a way to fix the SQL-injection. – Johan Apr 27 '11 at 23:21
I made the comment [an answer to your other question](http://stackoverflow.com/questions/5811834/why-would-this-be-poor-php-code/5811853#5811853). @Johan the only reliable way I know, as said, is to check against a list of known valid values (like from a `SHOW TABLES` command). – Pekka Apr 27 '11 at 23:23
@Pekka, thank you thought it was important to know, because I can't be the only one to make this beginners error. – Johan Apr 27 '11 at 23:33

Passing javascript variable into PHP and making mysql query

4 Answers4