PHP - Connecting to MySQL DB

[lubblig]

I'm trying to understand how PHP works and how to secure it when connecting to a MySQL database (DB created with PHPMyAdmin). I found this example on W3Schools:

 <?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?> 

And I'm kind of skeptical to the whole having the password stored like that in plain text. Is this the way to do it and make sure that the user being used in the PHP file has restricted access to the DB or is there any other ways of securing it? What is consider the industry standard?

 

Since I'm new to PHP and MySQL databases, I haven't really gotten to best security practices part yet (as I'm still learning and running it locally) but as I would like to put things on my webserver for access outside of my house (purely for me to test, but I still want to secure it as well as possible to learn how it works), I would like to make sure that I've secured everything. So if you've got any other tips or does/don'ts that might seem super obvious to you, please tell me because I likely don't know it! This includes PHP, HTML, JS, MySQL and PHPMyAdmin (and any other I might have forgotten) since this is what I'm currently using for it all.

 

Thanks!

[espurritado]

of the top of my head, you could store them in a separate file and use "require" for the variables. This does not remove your problem with the password in plain text, just moves it somewhere else.However, there should be no problem as long as you make sure there's no way to access the files inside the server from the outside since the front end won't be able to read the php scripts.

 

Just remember to put the file with the users and passwords in the gitignore if you ever publish it to github or some other platform. You won't believe how many sensible passwords can be found if you read through the config files of github projects.

[lubblig]

9 minutes ago, espurritado said:

of the top of my head, you could store them in a separate file and use "require" for the variables. This does not remove your problem with the password in plain text, just moves it somewhere else.However, there should be no problem as long as you make sure there's no way to access the files inside the server from the outside since the front end won't be able to read the php scripts.

 

Just remember to put the file with the users and passwords in the gitignore if you ever publish it to github or some other platform. You won't believe how many sensible passwords can be found if you read through the config files of github projects.

Would this be done with the .htaccess file? If so, how?

[espurritado]

7 minutes ago, lubblig said:

Would this be done with the .htaccess file? If so, how?

never really needed to do it, however, here is the same question at stackoverflow

http://stackoverflow.com/questions/3703449/how-to-prevent-php-files-from-being-downloaded-and-what-are-some-ways-someone-c

[lubblig]

2 hours ago, espurritado said:

never really needed to do it, however, here is the same question at stackoverflow

http://stackoverflow.com/questions/3703449/how-to-prevent-php-files-from-being-downloaded-and-what-are-some-ways-someone-c

In your link, they use the following code in the .htaccess file:

 

# In case there is no PHP, deny access to php files (for safety)
<IfModule !php5_module>
    <FilesMatch "\.(php|phtml)$">
        Order allow,deny
        Deny from all
    </FilesMatch>
</IfModule>
# the following should be added if you want to parse .php and .phtml file as PHP
# .phps will add syntax highlighting to the file when requesting it with a browser
<IfModule php5_module>
    AddType text/html .php .phtml .phps
    AddHandler application/x-httpd-php .php .phtml
    AddHandler application/x-httpd-php-source .phps
</IfModule>

The second line says php5_module, since I'm running the latest version of PHP (7), could I just replace the 5 with a 7 or has something changed since version 5 that would make it not work?

[vorticalbox]

storing the password like that is fine. No one other than yourself can see the password. The only way another person could see it would be to get access to your file on the server in which case reading a password is least of your worries.

 

I would suggest moving to PDO for your database connections. Currently on a phone so can't type any code up but if no one has replied with a snip will post one when im next on a machine.

 

 

[lubblig]

2 minutes ago, vorticalbox said:

storing the password like that is fine. No one other than yourself can see the password. The only way another person could see it would be to get access to your file on the server in which case reading a password is least of your worries.

 

I would suggest moving to PDO for your database connections. Currently on a phone so can't type any code up but if no one has replied with a snip will post one when im next on a machine.

 

 

Thanks!

Why PDO? I don't know what it is so I'm not questioning it, I'm just curious what I'll benefit from it. Is it more secure or easier to use? If so, why? I'll try to do some reading on it right now but I'd love to hear your input on it!

[vorticalbox]

42 minutes ago, lubblig said:

Thanks!

Why PDO? I don't know what it is so I'm not questioning it, I'm just curious what I'll benefit from it. Is it more secure or easier to use? If so, why? I'll try to do some reading on it right now but I'd love to hear your input on it!

 

 

Can be more secure I suggest reading up on it.

 

conn.php

<?php
class db {    
	private static $handle;    
	
	public static function connect($host, $username, $password, $db){        
		self::$handle = new PDO("mysql:host={$host};dbname={$db}", $username, $password);        
		self::$handle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );    
	}    
	
	public static function query($sql, $args=null){        
		$sth = self::$handle->prepare($sql);        
		$sth->execute($args);        
		return $sth;    
		}
	
}

//connect to Database
db::connect("127.0.0.1", "root", "", "blog");

?>

then in your files

index.php

require_once('conn.php');

//all SQL queries like this
$sql = db::query("SELECT * FROM Posts ORDER BY date DESC");

//or with paras
$sql = db::query("SELECT * FROM Posts WHERE ID=:id",
					array(":id"=>$_GET['ID']));

All the connection checking is done in the class so you don't have to re-write it a lot of times. Will say that some queries with quotes can cause issues but something to learn how to fix