Secured PHP Query

[bharanidharanit]

Does this query the secured one?

                database_connect()
                $query="SELECT id
				FROM users
				WHERE username='$un';";
		$result =mysql_query($query) or
		dir(mysql_error($dbconn));
		if(mysql_num_rows($result) < 1)
		{
			die("No such users, Cant create directory");
		}
		$userdata=mysql_fetch_array($result,MYSQL_ASSOC);
		$userid = $userdata['id'];
		mkdir("images/avatar/".$userid,0777) or
		dir("Cant create directory");

[kows]

no. the whole point of a "secure query" is to sanitise a user's input. here, you're just taking the user's input (presumably $un) and inserting it into your query. you must sanitise input before using it if you're looking to make something secure. in this case, you would be looking to use mysql_real_escape_string():

  $un = mysql_real_escape_string($_POST['username']);
  $sql = "SELECT id FROM users WHERE username='{$un}';"; 

also, you're calling the dir() function a bunch of times, probably rather than die(). dir() creates an instance of the Dir class -- die() will kill execution of your script.

[bharanidharanit]

sorry i mistyped dir() instead of die().
Ya i am already using mysql_real_escape_string in the beginning page, but i din't show here.
I came to hear of the SQL injection. using this alone make the query secured? or Do i need to use paramaterized query?
If so what is really happening when using parameters.

[I_Love_My_Vans]

1) Nobody can really tell if your code is secure if you purposely miss bits out.
2) If you use mysql_real_escape_string on each of your user inputs then you should be fine.

[bharanidharanit]

Ya that's right, but here in this case, i am querying id for the corresponding username, i am using the same username for many cases and so i once used that function at the beginning. But i must shown that here and i missed.
Thankyou

[penagate]

Use mysqli or PDO, and change your code to use prepared statements with parameters. This avoids any risk of SQL injection.

[visualAd]

Does this query the secured one?

                database_connect()
                $query="SELECT id
				FROM users
				WHERE username='$un';";
		$result =mysql_query($query) or
		dir(mysql_error($dbconn));
		if(mysql_num_rows($result) < 1)
		{
			die("No such users, Cant create directory");
		}
		$userdata=mysql_fetch_array($result,MYSQL_ASSOC);
		$userid = $userdata['id'];
		mkdir("images/avatar/".$userid,0777) or
		dir("Cant create directory");

You tell us!! Its a snippet poorly written code, so I can only assume that it has come from a poorly written application which is insecure.

On its own and from the looks of things you are using auto globals and creating directories with global write permissions which makes it very insecure.

[bharanidharanit]

On its own and from the looks of things you are using auto globals and creating directories with global write permissions which makes it very insecure.

How to make it secured?

[bharanidharanit]

Hi i searched for PDO coding, and i get this, but i am running with errors. Whats error here?

try{
$dbh=new
PDO('mysql:host=localhost;dbname=alejandro','user','password');
$dbh->prepare('SELECT * FROM users WHERE name=? AND email=?');
$dbh->execute(array('Alejandro','[email protected]'));
$result=$dbh->fetchAll();
// displays data for 'Alejandro'
print_r($result);
$dbh->execute(array('John','[email protected]'));
// display data for 'John'
print_r($result);
}
catch(PDOException $e) {
echo 'Error : '.$e->getMessage();
exit();
}
As shown above,

Also for passing one value, why are they using array?

[bharanidharanit]

Hi, i debugged the error and also found the use of array.

<?php

try{
$dbh=new
PDO('mysql:host=localhost;dbname=mvb22','root','admin');
$sth=$dbh->prepare('SELECT id,username,userpwd FROM users WHERE username=?');
$sth->execute(array('bharani'));
$result=$sth->fetchAll();
print_r($result[0][0]);
}
catch(PDOException $e) {
echo 'Error : '.$e->getMessage();
exit();
}

?>

[visualAd]

Replacing your code with that won't work, because the code above does something different from the code you posted. If you wish to use PDO you need to modify your code; you also need to ensure the PDO_Mysql driver is installed and enabled (this can be checked with a call to phpinfo()).

In response to your previous question, I would suggest the following at minimum:

• If you are not already, get the user submitted variable from the $_POST or $_GET super-globals and go to your PHP.ini and turn register_globals off. If your host will not let you, find another host.
  
  
• If you are inserting any variables into your script, you need to use mysql_real_escape_string on them before hand. If you do not, they can be populated with SQL and be used to do all kinds of nasty stuff.
  
  Ideally, you should use either mysqli or PDO. These extensions support parametized queries which will automatically escape the variables for you.
  
  
• Remove access to the images directory by either moving it above the root pages of the website, or by modifying the permissions so that it is not globally writeable (this may not be possible with some setups). At the very least, if you do not want users to have direct access to the directory, you should put an .htaccess file into it with the following lines:
  
  

  Order Allow, Deny
  
  Deny From All

[bharanidharanit]

Replacing your code with that won't work, because the code above does something different from the code you posted.

Ya may be the codes are different, but the first one throwed me an error, whereas the second does not. The second one gives me output what i needed.
Also the second one i referred from PHP Manual, and i think i can use that. ???

If you are not already, get the user submitted variable from the $_POST or $_GET super-globals

I am using $_POST already,

go to your PHP.ini and turn register_globals off

Why to do this? I am not sure; only if it is on,i can able to get variables from $_POST or $_GET.

Remove access to the images directory by either moving it above the root pages of the website, or by modifying the permissions so that it is not globally writeable (this may not be possible with some setups). At the very least, if you do not want users to have direct access to the directory, you should put an .htaccess file into it with the following lines:

But when the users uploads their files, it must be copied to the images directory. When i remove access to that directory means, how the can user do that?

Also .htaccess, i tried it before, and i read that many hosts does not support it. Is that really true?

[visualAd]

Ya may be the codes are different, but the first one throwed me an error, whereas the second does not. The second one gives me output what i needed.
Also the second one i referred from PHP Manual, and i think i can use that. ???

If the second one works, you should probably use that I wouldn't recommend posting your mysql username and password on the Internet though and I wouldn't recommend using the root account either.

I would also discourage the printing of the exception message. These are in effect, error messages and can reveal sensitive infrormation about your application the the server on which it resides.

Why to do this? I am not sure; only if it is on,i can able to get variables from $_POST or $_GET.

Because register globals can be used by an attacker to poisen uninitialised variables in your script with their own input. You can check if it is on by using a call to php.ini.

But when the users uploads their files, it must be copied to the images directory. When i remove access to that directory means, how the can user do that?

At the very least, your PHP scripts will have access to directories above the root of the website. These directories are not accessible by the user but can be accessed by you.

If your PHP scripts run under a different user ID from the webserver, you can change the permissions on the directory to make it inaccessible to Apache and thus users who attempt to access.

Also .htaccess, i tried it before, and i read that many hosts does not support it. Is that really true?

Its not the best way to deny access to a directory. If you host gives you access to the Apache config files they will probably disable .htaccess files. The best thing to do is check.

[bharanidharanit]

Ya thankyou.

At the very least, your PHP scripts will have access to directories above the root of the website. These directories are not accessible by the user but can be accessed by you.

If your PHP scripts run under a different user ID from the webserver, you can change the permissions on the directory to make it inaccessible to Apache and thus users who attempt to access.

HI again i am asking the same one. But i want the users to add their photos or some files into the directory, and without directory permission how can they do that?

[kows]

users don't need to be able to access the directory for your script to be able to upload images to that directory. you can chmod the directory so that you have access to it (owner) and the user does not have any access to it. this will force you to use a script to fetch those images though, rather than just having a regular <img> tag to retrieve the images. you could just allow the users read access but not write, though. it depends how you want to deal with stuff.