Get data from mysql using variable

Question

I am using this code

<?php
  $word = $_POST['word'];
  $wid= $_POST['id'];
  print "<table>";
  print "<tr>";
  $sql= 'SELECT url_imgsrch FROM p_url_imgsrch where 'word_id'='[$wid]' ORDER BY RAND() LIMIT 5';
  $result   =   mysql_query($sql);
  while($row = mysql_fetch_array($result)){

           print ' <td>
               <img name="myimage" src="'.$row[0].'" width="100" height="100" alt="word" border="1"/>
            </td>';
  }
  print "</tr>";
  print "</table>";
  ?>

What I am doing is to get one field from mysql using where clause, but it shows an error

Parse error: syntax error, unexpected T_STRING in D:\wamp\www\demo\login\card.php on line 21

and line 21 holds

$sql= 'SELECT url_imgsrch FROM p_url_imgsrch where 'word_id'='[$wid]' ORDER BY RAND() LIMIT 5';

Kindly guide me what is the blunder I am doing? Guideline please.

One thing I think I should make clear is "ord_id field" is Numeric(int)

Answer 1

On this line:

$sql= 'SELECT url_imgsrch FROM p_url_imgsrch where 'word_id'='[$wid]' ORDER BY RAND() LIMIT 5';

Notice specifically how Stack Overflow's syntax highlighter treats it, especially around the term word_id. What you're doing with those single-quotes is terminating the PHP string and then throwing in an unknown term, word_id. PHP doesn't know what to do with this, so it gives the error you're seeing.

Is there a reason you're using single-quotes around the term word_id? Should it be a string in the SQL statement? I'm guessing it shouldn't. You should be able to just reference the column in the table directly in the query. Something like this:

$sql= 'SELECT url_imgsrch FROM p_url_imgsrch where word_id='[$wid]' ORDER BY RAND() LIMIT 5';

Note that the PHP syntax parsing is completely separate from the SQL syntax parsing. All you're doing in this code is building a string to send to the database. The database will, afterward, parse that string as SQL code. So mixing PHP and SQL should be done with care so as to not produce invalid SQL, or you'll get more errors even though your PHP code is fine. (You should also, as noted in a comment on the question and in other answers, look into things like SQL Injection Attacks and learn how to further protect your code. The code may work, but it may at the same time present glaring security holes. See the rest of this answer, and other answers, for more details on this. It is important.)

Quick question, and maybe this is just syntax with which I'm not immediately familiar... why are there square brackets around the $wid variable in that statement? I'm more familiar with MSSQL than with MySQL, and in the former square brackets signify a database object (not a variable, such as a string to match against a database object), which doesn't seem to be what you want here. It's likely you actually mean this:

$sql= "SELECT url_imgsrch FROM p_url_imgsrch where word_id='$wid' ORDER BY RAND() LIMIT 5";

Note two differences:

1. Got rid of the square brackets.
2. Changed the first and last quotes of the line from single quotes to double quotes. Both single quotes and double quotes can be used to denote strings in PHP. In this particular case, the double quotes are useful because they allow you to include single quotes within the string itself (without having to be escaped, which would make it more difficult to read).

Finally, and as others have also pointed out, this code needs to be protected against SQL injection attacks. The most immediate and apparent way to do this is with mysql_real_escape_string(), more information here. What this function essentially does is convert a string into a more SQL-safe string by escaping control characters and such. You'd wrap any and all input strings with this before adding them to the SQL string:

$wid = mysql_real_escape_string($wid);
$sql= "SELECT url_imgsrch FROM p_url_imgsrch where word_id='$wid' ORDER BY RAND() LIMIT 5";

You can also consider taking further steps to reduce your SQL vulnerabilities, as well as potentially result in cleaner code. Consider looking into PHP Data Objects to represent your database interactions instead of just building SQL strings directly in code.

Answer 2

This should work fine when using double quotes for your sql statement $sql= "SELECT ..."

WHERE word_id='$wid'

Also add mysql_real_escape_string() to user input variables to avoid SQL Injections:

$wid = mysql_real_escape_string($_POST['id']);

Answer 3

try this:

$wid = mysql_real_escape_string($wid);
$sql = "SELECT url_imgsrch FROM p_url_imgsrch where word_id = '$wid' ORDER BY RAND() LIMIT 5";

You were improperly using quotes. If you are going to use quotes on column names, use the backtick, not the single quote.

Answer 4

As others have mentioned you aren't using quotes correctly if you use 'single quotes' you'd have to concatenate the strings as so:

'string ' . $var

the single quotes mean that everything within is regarded as a literal string where almost nothing has to be escaped (except for single quotes of course). Inside "double quotes" you you have to escape special characters, also you are allowed to use variable names without the need to append the variable to the string yourself:

"string $var" 

for instance will work fine... See the PHP documentation for more info

You're code is susceptible to SQL-Injection. You can see some answers on how to fix it here. You can use the mysql_real_escape_string() to escape certain control characters as mentioned, but it is not failsafe. Example taken from Polynomial on the Link before:

SELECT * FROM users WHERE score = $var
//is still vulnerable to
$var = "1 OR 1 = 1"

I would recommend you use prepared statements if you consider to use it online.

EDIT:

Changed example.

added Links