PHP converts space to underscore

The_B_Man

I have a problem with an insert statement. it gets its inputs from a form on the previous page. the form sends the data over the querystring and i retrieve the values using $_GET["watever"]. in the querystring, one item in particular is giving me problems: engine size. when i echo the sql statement it has been converted from "engine+size" up on the querystring, to "engine_size" in the sql statement causing an error in the sql statement as there is no field with that name. below is the code that changes it. can anyone see the problem? thanks.

foreach ($_GET as $k => $v)
    {
    if ($k != "tablename" && $k != "action" && $k != "step")
        {
        $s_Fields .= $k."`, `";
        $s_Values .= "'".$v."', ";
        }
    }
$s_SQL = "INSERT INTO `".$_GET["tablename"]."` (`".substr($s_Fields, 1, -3);
$s_SQL .= ") VALUES (".substr($s_Values, 1, -2).")";

.

rsynnott

Okay, I think your problem MAY be that '+' is a special character in HTTP GET strings. Try urlencoding it on the other side.

But besides that, you should NEVER do what you've done there. Do you realise you're giving the casual user complete control over your database?

The_B_Man

eh what? complete control? thats not good. should i use htmlspecialchars on the variable sor something?

daymobrew

Among other things I guess you shouldn't get the table name from the user, only data for the table.
And you should validate data e.g. that you get numbers when you expect numbers and they are within range.

Look at: http://www.php.net/manual/en/security.database.php and http://www.php.net/manual/en/security.database.sql-injection.php

The Corinthian

Odd problem. Have you echo'ed the input to see at what point the space is being replaced with an underscore?

The_B_Man

the user never actually types in the name of the table. the table name is got from a drop down box 2 pages earlier, then a form with the corresponding fields are displayed with text boxes beside them so the user can enter data. Then it gets sent to the third page, which the code above is taken from, and thats where i get my error. i echoed the sql and heres what i got:

INSERT INTO `cars` (`company`, `model`, `engine_size`, `colour`, `price`, `sale`, `stock`, `section`, `subsection`) VALUES ('Porsche', '911 Carrera', '4 litre', 'Silver', '99,999.00', 'no', '8', 'Cars', 'Sports')
Error in SQL: Unknown column 'engine_size' in 'field list'

The error is with the "engine_size" bit. In the "cars" table, it is "engine size", with a space.

This is the querystring:
index.php?company=Porsche&model=911+Carrera&engine+size=4+litre&colour=Silver&price=99%2C999.00&sale=no&stock=8&section=Cars&subsection=Sports&action=add_item&tablename=cars&step=3

Kernel32

rsynnott wrote:

But besides that, you should NEVER do what you've done there. Do you realise you're giving the casual user complete control over your database?

Very good point and always worth pointing out. Do some research on SQL Injection..

index.php?company=';Drop Table cars;--&action=add_item&tablename=cars&step=3

If the user attaching to the database has enough permissions then a querystring like this could very well do damage. My guess is if you would allow this in the first place your are also probably using a user with a high level of permissions because it easier than locking it down.

cormy

The_B_Man wrote:

The error is with the "engine_size" bit. In the "cars" table, it is "engine size", with a space.

This is the querystring:
index.php?company=Porsche&model=911+Carrera&engine+size=4+litre&colour=Silver&price=99%2C999.00&sale=no&stock=8&section=Cars&subsection=Sports&action=add_item&tablename=cars&step=3

You are actually still giving the client the ability to enter the table name - i.e. the url contains 'tablename=..' which could just as easily be changed to something else by typing an alternative value (i.e. tablename=users / tablename=mysql etc.) into the url string and hitting return (in the browser).

Also have you considered what would happen if a user supplied the following as part of the url ...

....&colour=bladeblah;drop table cars&....

cormy

"Snap" Kernel32!

The_B_Man

well first priority is fixing the "space" bug. sure i can just change everything to "POST". that'll help. plus its for a college project so i can do the security later.

cormy

Nope - changing to POST still doesn't protect you. All I'd have to do is create a html page containing a form on my PC, set the action=http://yourwebsite.com/formprocessor.php (or whatever), sent the method=POST, fire up my browser, load my html file, fill in the offending data and hit submit .... same effect. You can't depend on anything coming from the client being bona fide - hence input validation is so important.

Just so you don't think I'm not trying to help with your spaces problem however (!) I actually tested your php script above myself (yes I'm bored), and I can confirm that I reproduced the same problem. I'm not sure if there's a way around it - see ...

http://www.mail-archive.com/php-general@lists.php.net/msg94287.html

The_B_Man

hmmm not looking too good. in my admin section, i have an "Add Section" option which the user can enter in the field names and create a table. if i could find a way to convert the space to an underscore myself then that would solve the problem later in the "Add Product" section. Isn't there a function that will replace any character in a string wit a supplied character? str_replace or something?

Kernel32

The_B_Man wrote:

well first priority is fixing the "space" bug. sure i can just change everything to "POST". that'll help. plus its for a college project so i can do the security later.

I know the thread has been moved off topic by the security subject but this is such an important topic. The statement above is exactly why we have all these security problems in software. It starts in college or when its just a hobby and progresses from there. If I were a professor and was handed code like that I would fail the project, regardless of it meeting the functional requirments. I manage a team of developers and if anyone produced code like that they would be required to fix it. If the developer persisted in producing code like that they would end up looking for another job.

is_that_so

Kernel32 wrote:

Very good point and always worth pointing out. Do some research on SQL Injection..

index.php?company=';Drop Table cars;--&action=add_item&tablename=cars&step=3

If the user attaching to the database has enough permissions then a querystring like this could very well do damage. My guess is if you would allow this in the first place your are also probably using a user with a high level of permissions because it easier than locking it down.

And my comment off-topic.
One way to do this is to create a generic user that has defined restricted permissions and use that for general db processing. Give only the absolute minimum. This is more important than getting an insert to work.

The_B_Man

On the previous page I use "htmlspecialchars()" on any user input. that sorta helps a bit but im working on making the database more secure. Would you suggest I also use it on the above code? Theres some good info on http://ie.php.net about the sql injection mentioned above. coincidentally i was only reading about it earlier today. my project in due 25th april so im not sure if i can finish it and add in all the security measures too.

rsynnott

The_B_Man wrote:

On the previous page I use "htmlspecialchars()" on any user input. that sorta helps a bit but im working on making the database more secure. Would you suggest I also use it on the above code? Theres some good info on http://ie.php.net about the sql injection mentioned above. coincidentally i was only reading about it earlier today. my project in due 25th april so im not sure if i can finish it and add in all the security measures too.

No, use addslashes, and stripslashes when youire taking stuff out of the DB. Otherwise, anyone on the internet can drop your tables, modify the data, give themselves priviledged accounts if you have an accounts-based thing, and so on. They may also be able to compromise your website, if it's on the same server as MySQL.

is_that_so

Off topic again. :rolleyes:
I am assuming that this is a college project. I am inclined to suggest that if this is just a bog-standard college project and not part of a final year submission you might be OK. Usually you get to say "If I had more time....."
At the very least if you can't resolve the issue, acknowledge you know about it by explaining what it is and how it works and the effect it can have.
However if it is a final year submission, then burn lots of midnight oil.
If it's commercial, then scream for HELP very, very loudly.

delinear

As to the original question, I was having exactly the same problem but with $_POST rather than $_GET variables. I had written an automailer which used form field names as labels in the email but labels with spaces (like "company name") were having underscores inserted ($_POST).

The only way I could come up with to resolve this was to use the following (I needed to keep the values as $_POST for processing later in the script, otherwise I'd have just converted them to a new array without changing them back to POST):

[php]
foreach($_POST as $key => $value) {
$key = str_replace("_", " ", $key);
$key = trim($key);
$new_post[$key] = $value;
}
unset($_POST);

foreach($new_post as $key => $value) {
$_POST[$key] = $value;
}
[/php]

The same should work for $_GET variables, just change the $_POST references to $_GET references and insert the script before you use any of the GET data.

As for the security issue, it's worth checking whether magic_quotes_gpc is enabled - it's on by default these days. If it is then you don't need to use addslashes as slashes will be added automatically by the system (although personally I always disable this automatic replacement and code it manually - it's good practice to do it this way because you could always encounter a system where magic_quotes are off).

Try something like:

[php]
if(get_magic_quotes_gpc) {
echo "Magic quotes are enabled, no need to use addslashes";
} else {
echo "Magic quotes are disabled, you need to addslashes manually!";
}
[/php]

The_B_Man

hey thanx delinear.

about "addslashes" & "stripslashes": i was looking on the PHP site and comparing them to "htmlspecialchars" and I still cant find a valid reason for me to change from htmlspecialschars to add/strip slashes. they all seem similar, if not identical in the situations I encountered in my site. can anyone shed any light on any reason I cant see to use addslashes? or maybe shud i use it in conjunction with htmlspecialchars? i saw an example where both were used together.

dahamsta

What delinear is doing will work, but it's a hack, not a fix. It looks to me like you need to go back through your code and print from various points, to find out where the conversion is happening.

adam

The_B_Man

ye dahamsta, i found exactly where it was converting. so before it got there it just used str_replace to replace spaces with underscores so the problem doesnt arise!

delinear

The_B_Man wrote:

hey thanx delinear.

about "addslashes" & "stripslashes": i was looking on the PHP site and comparing them to "htmlspecialchars" and I still cant find a valid reason for me to change from htmlspecialschars to add/strip slashes. they all seem similar, if not identical in the situations I encountered in my site. can anyone shed any light on any reason I cant see to use addslashes? or maybe shud i use it in conjunction with htmlspecialchars? i saw an example where both were used together.

Addslashes and htmlspecialchars do different things, really; htmlspecialchars is only really designed to stop code outputting to the browser and messing up your pages. Addslashes, on the other hand, is designed to protect your database from malicious attacks which could give hackers access to confidential information or even allow them to wipe out your whole database.

A simple example. Imagine you have the following script that allows a user to enter their username and password in a form and log into your site to view private information:
[php]$link = mysql_connect($server, $username, $password);
mysql_select_db($database);

$query = "SELECT * FROM users WHERE username='" . $_POST . "' AND password='" . $_POST . "'";
$result = mysql_query($query);
$rows = mysql_num_rows($result);


if ($rows > 0) {
$_SESSION == yes;
header('Location: confidential.php');
} else {
echo 'You could not be logged in';
}[/php]

Now, if the user instead entered something like ' OR 1=1 MySQL would see your query as "SELECT * FROM users WHERE username='username' AND password='password' OR 1=1" - because 1 always equals 1 the user will be logged into your site without having to even guess at a password! Obviously this is just an example of what could happen, but as you can see it's pretty serious.

However, if you used addslashes, the quotations get escaped before they are entered into your database. If you change your query to this:

[php]$query = "SELECT * FROM users WHERE username='" . addslashes($_POST) . "' AND password='" . addslashes($_POST) . "'"; [/php]

Then when the user inserts their malicious query MySQL will see it as "SELECT * FROM users WHERE username='username' AND password='password\' OR 1=1" and MySQL knows that the escaped character should be interpreted as part of a string, not as a quote so all that will happen is that the database will treat the additional entry as part of the password string and return 0 rows, the correct behaviour.

Simply put, any time you insert data into the database, use addslashes($data). Any time you want to display data from the database in your browser, use stripslashes($data).

Seriously, though, go read the millions of sites that deal with the dangers of SQL injection - there are loads of them out there and you will regret it if you don't.

Good luck with the project