Comparing a string (variable) in a WHERE statement

Question

I've got PHP script that uses an SQL query to save JSON in a variable. The query is something like this (I've changed the names of the columns to something more general)

$value = $_GET['value'];

SELECT column1 AS 'col1', column2 AS 'col2', column3 AS 'col3', column4 AS 'col4', column5 AS 'col5'
FROM db.dbo.table
WHERE column5 = $value

The variable comes from another page, and it is a string that may or may not contain a space. However, this does not return any JSON and I'm not sure why (It just returns []). This example script works perfectly in SQL Server, but when I try to run it in PHP, it doesn't work. If I get rid of the line WHERE column5 = $value, the script returns JSON. This is the line that passes the variable in a JS script on the other page

return '<a href="/script.php?value=' + data + '" target="_blank">Example</a>'

I'm sure it's something minor, but can someone tell me what is wrong? Thanks

Missing quotes. ...WHERE column5 = '$value'. Also don't forget escaping the params. — Fracsi
– Fracsi, Commented Jul 15, 2013 at 12:46

LSerni · Accepted Answer · 2013-07-15 12:57:28Z

2

There are two problems with your approach (apart from not using prepared statements).

The first is that you trust user input:

$value = $_GET['value'];

This should be replaced by some kind of escaping of $value, so that one can't execute arbitrary queries (e.g. in a SUBSELECT) and cause denial of services, or worse, if some query allows issuing DELETE or DROP commands.

The second problem is that the value may contain spaces, and this would break SQL syntax unless you added quote signs:

SELECT column1 AS 'col1', column2 AS 'col2', column3 AS 'col3', column4 AS 'col4', column5 AS 'col5'
FROM db.dbo.table
WHERE column5 = '$value'

With a judicious use of prepared statements, you can defuse both issues in one go.

Another facet of the same second problem is that you might run into escaping problems, if the value passed through the URL contains URL-quoted characters (e.g. +, % and so on), or encoding problems if the value crosses an encoding boundary (e.g. the originating page is, or is automatically recognized as, or might be forced into being recognized as, ISO-8859-15 and the database is UTF-8, or vice versa. This last issue has no "one-size-fits-all" solution - even if prepared statements may help -, and requires you to check carefully either the encoding path of your variables, or force everything to always be converted to UTF-8. You can verify whether you are at risk by placing international characters (e.g. "à") inside the value in the page containing the link, and seeing what happens.

answered Jul 15, 2013 at 12:57

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TFischer Over a year ago

The value that is passed to the query doesn't come from a user input, so I don't think this is necessary. But this would be a better answer for anyone else with this problem.

user1864610 · Accepted Answer · 2013-07-15 12:47:33Z

2

Add quotes to your where clause:e.g. where column5 = '$value'

answered Jul 15, 2013 at 12:47

user1864610

2 Comments

TFischer Over a year ago

Oh, okay. I guess I just got confused since this worked for my other script, but that variable was an integer. I forgot that strings need to be wrapped in quotes. Thanks!

user1864610 Over a year ago

You're welcome. If you have what you need please be kind enough to accept the answer. Thanks

Nanhe Kumar · Accepted Answer · 2013-07-15 12:50:37Z

2

$query="SELECT column1 AS 'col1', column2 AS 'col2', column3 AS 'col3', column4 AS 'col4', column5 AS 'col5' FROM db.dbo.table WHERE column5 ='". $_GET['value']."'";
OR
$query="SELECT column1 AS 'col1', column2 AS 'col2', column3 AS 'col3', column4 AS 'col4', column5 AS 'col5' FROM db.dbo.table WHERE column5 ='". $value."'";

answered Jul 15, 2013 at 12:50

Nanhe Kumar

16.4k5 gold badges84 silver badges72 bronze badges

Comments

Nil'z · Accepted Answer · 2013-07-15 12:47:50Z

0

Try:

$sql = "SELECT column1 AS 'col1', column2 AS 'col2', column3 AS 'col3', column4 AS 'col4', column5 AS 'col5' FROM db.dbo.table WHERE column5 = '$value'";

answered Jul 15, 2013 at 12:47

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

Collectives™ on Stack Overflow

Comparing a string (variable) in a WHERE statement

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related