PHP & SQL Space issue

Question

I've got a strange problem, have a look at this code:

$query="SELECT device_name, ip, method, port
        FROM devices
        WHERE device_name='".$_POST['name']."'";

When I post in 'name' -> 'test' (text without spaces) it works (returns some values I want). But in this situation for example 'name' -> 'test test' (text with space) it returns empty values, but shouldn't.

At last, when I tried this:

$query="SELECT device_name, ip, method, port
        FROM devices
        WHERE device_name='test test'";

It returned the values I want.

Why does it make problem when I use $_POST['name'] in my SQL code? Certainly, I've tried saving $_POST['name'] in a variable and then put it into the SQL code, also using ( `` ), ( [ ] ) than ('') doesn't do the trick. I am working with MySQL.

Any ideas?

The var_dump() of $_POST['name']:

<pre class='xdebug-var-dump' dir='ltr'><small>string</small> <font color='#cc0000'>'WIM'</font> <i>(length=3)</i>

And I see, that $_POST['name'] contains only the word after the space. Why?

**WARNING**: This looks [terrifyingly insecure](http://bobby-tables.com/) and for your sake I hope this is not on the public internet. You need to ensure any and all user parameters are [properly escaped](http://bobby-tables.com/php) or you are at serious risk of an application compromise. Whenever possible use prepared statements and placeholders to ensure you're not exposed to errors of that sort.The way you're composing the query here is part of the problem. The rest depends on what data you have in your table. — tadman, Dec 12 '14 at 19:27
Can you please add to your post the exact output of `var_dump( $_POST[ 'name' ] )` (and tadman is 100% correct. PLEASE use PDO or at least bind_param) — Don Rhummy, Dec 12 '14 at 19:28
have you tried `trim($_POST['name'])` to make sure there are not any leading/trailing spaces that you don't see. — Sean, Dec 12 '14 at 19:32
@DonRhummy I've added the var_dump. BTW, do the users connected with some post see when the question was edited? It is a simple local app, I know, that it's not a good way to put $_POST inside the query, but I'm interested why it isn't working. — pavon147, Dec 12 '14 at 19:47
This isn't a SQL issue, but a CGI issue. How are you posting your data to the PHP? — Bart Friederichs, Dec 12 '14 at 19:50
OK, I've found the reason. Please, don't ask me what it was. I'm sorry and thank you for the advices about prepared statements. Thanks! — pavon147, Dec 12 '14 at 19:58

score 1 · Answer 1 · answered Dec 12 '14 at 19:29

Most probably, $_POST['name'], is in fact not test test, because if it was, and the rest you say is true, this works.

That being said: don't do this

NEVER put $_POST variables (or any other non-validated data from "outside") directly in your query string. It opens you up to SQL injection. You should use prepared statements instead, read up on PDO or MySQLi.

PHP & SQL Space issue

1 Answers1