Jump to content
Search In
  • More options...
Find results that contain...
Find results in...

PHP - Connecting to MySQL DB

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!

Spoiler

System:

i5 3570k @ 4.4 GHz, MSI Z77A-G43, Dominator Platinum 1600MHz 16GB (2x8GB), EVGA GTX 980ti 6GB, CM HAF XM, Samsung 850 Pro 256GB + Some WD Red HDD, Corsair RM850 80+ Gold, Asus Xonar Essence STX, Windows 10 Pro 64bit

PCPP:

http://pcpartpicker.com/p/znZqcf

 

Link to post
Share on other sites

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.

The best way to measure the quality of a piece of code is "Oh F*** "s per line

Link to post
Share on other sites
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?

Spoiler

System:

i5 3570k @ 4.4 GHz, MSI Z77A-G43, Dominator Platinum 1600MHz 16GB (2x8GB), EVGA GTX 980ti 6GB, CM HAF XM, Samsung 850 Pro 256GB + Some WD Red HDD, Corsair RM850 80+ Gold, Asus Xonar Essence STX, Windows 10 Pro 64bit

PCPP:

http://pcpartpicker.com/p/znZqcf

 

Link to post
Share on other sites
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?

Spoiler

System:

i5 3570k @ 4.4 GHz, MSI Z77A-G43, Dominator Platinum 1600MHz 16GB (2x8GB), EVGA GTX 980ti 6GB, CM HAF XM, Samsung 850 Pro 256GB + Some WD Red HDD, Corsair RM850 80+ Gold, Asus Xonar Essence STX, Windows 10 Pro 64bit

PCPP:

http://pcpartpicker.com/p/znZqcf

 

Link to post
Share on other sites

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.

 

 

                     ¸„»°'´¸„»°'´ Vorticalbox `'°«„¸`'°«„¸
`'°«„¸¸„»°'´¸„»°'´`'°«„¸Scientia Potentia est  ¸„»°'´`'°«„¸`'°«„¸¸„»°'´

Link to post
Share on other sites
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!

Spoiler

System:

i5 3570k @ 4.4 GHz, MSI Z77A-G43, Dominator Platinum 1600MHz 16GB (2x8GB), EVGA GTX 980ti 6GB, CM HAF XM, Samsung 850 Pro 256GB + Some WD Red HDD, Corsair RM850 80+ Gold, Asus Xonar Essence STX, Windows 10 Pro 64bit

PCPP:

http://pcpartpicker.com/p/znZqcf

 

Link to post
Share on other sites
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 :P

                     ¸„»°'´¸„»°'´ Vorticalbox `'°«„¸`'°«„¸
`'°«„¸¸„»°'´¸„»°'´`'°«„¸Scientia Potentia est  ¸„»°'´`'°«„¸`'°«„¸¸„»°'´

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×