i'm a newb at this and i want to approach this the best way possible. for the main connection to the database, i created an account called "visitor". this account has select privilieges only. it will be used to read some tables and spit out info. i have another connection called dealer. this one has select and insert privilieges. this connection is used to insert a timestamp into the database whenever someone logs in, everything else defaults back to the visitor connection. both these accounts don't have passwords. i've used the mysql_real_escape_string() to protect my data from injection. is there anything i'm doing wrong? do i need 2 connections or should i just use one?
I think you're probably OK with a single user with select and insert permissions. Use a password though. You should always have a password regardless of the permission of the user. Make sure you are cleaning any input that will be going into the database. Don't ever do anything like "INSERT INTO table VALUES ($_GET[''], or $_POST[''].... mysql_real_escape should cover most anything else.
Multiple users is a reasonable precaution but certainly set passwords on all user accounts in case something occurs to your db which allows (even temporarily) external connections to be made.
I believe it is best not to allow external connection, unless you got no other choice. If you are hosting application and the database on the same server, you don't need external connection. Otherwise, allow external connection to a fixed IP address only. Please note that mysql connection are by default not encrypted.
Absolutely agree however there can be mistakes made, files become corrupt etc that can cause settings to change and thus allow things like external connections, hence why "better safe than sorry" and use passwords
here's the thing. the connection info including username and pass is stored in an include text file. is there any way someone can find out the name of the file and access it? viewing the source through your browser won't reveal the php code, but is there another way other than naming the files random things and hoping people won't guess and get lucky? and how are you supposed to protect your include directory? just with a simple index.htm? and i'm confused about "external connections". what do you mean? this is for a public website...
They shouldnt be in a file ending in txt as the web server will serve these straight up. We dont use PHP but you should be able to hold them in a file with a .php extension and thus even if the person does find the file name it wouldnt serve up the content. SQL servers can be set up to allow connections from 1) other applications on the same physical machine 2) other applications on the same network 3) other applications anywhere (ie over the internet). An external connection is simply one that comes under option 3.
If you setup your user to access the database only from localhost, then even hacker can somehow steal the database username/password. They still have no way to connect to the database from external connection. So it is very important to keep that in mind when you create database user. No external IP/host unless it is absolute required.
If you have a typical config file (say config.php) that contains the settings for your db connection it would look something like: <?php $user="username"; $password="secret"; ?> Code (markup): Now if someone where to load that file in their browser directly the web server would process the php and they'd simply get a blank page. As an added precaution however you can store your config file outside the docroot. If you have a typical setup your web files will be under a /home/username/public_html directory or something similar. You can put your config file outside the docroot - say directly in the /home/username directory and include it. That way even if your webserver is misconfigured and serves up the raw php code no one will be able to get to it. By external connections people mean that you can setup MySQL to allow connections only from the same server or to allow connections from external servers over the internet. In a typical setup you'll have a php script which is running on the same server as the MySQL db and therefor it's not making an external connection but just a local connection. In that case if you prevent external connections entirely no one will be able to get in from the outside world.