Online Database Security

[alanschu]

The Webhosting my friend is using does support PHP (among other things) and MySQL.

 

 

Now, in my experiences connecting to databases in source code or HTML has always required you to enter your username and password into the source, which obviously isn't secure.

 

My friend would like to have a comment box on some pages of his website. Reading up on how to do that, it doesn't really seem that difficult. However, how can I prevent someone from seeing the script (even if it's in its own .php file) so that he can't see the username and password?

 

Another alternative is I create an account with limited access, but that account will still need to be able to write to the database (in order to add new comments) in addition to simply reading them.

 

Any ideas?

[Spider]

Now, this looks like a bigger project than I initially though. My knowledge in the world of php is limited, so I can't answer that question (if you were talking asp.NET on the other hand, I'm your man).

 

Just a though, have you considered using a good CMS as the foundation for the site? it'll save you a lot of hassle (and will help with the database stuff).

 

For PHP I've heard good things about Drupal, but my own experience with it isn't stellar (but again, PHP is not my thing, so that's a big reason why). On the asp.NET side, I can recommend umbraco, which is a fantastic piece of software. I'm in love with it.

[samm]

So... If anyone can access the PHP and read the script, you have either done something seriously wrong, or they have broken into the server anyway, so who cares if they can read the DB's password? PHP is ALWAYS interpreted before the user sees anything of it. Just make sure that the hoster doesn't display error messages to the public, or they can guess some about the internal structure of the server and about your script in case of an error.

 

Anyway: Four steps:

 

1. If they only need to enter comments, don't let them write to the database.

 

2. Create a new user, grant him EXECUTE on the ONE table used for comments (and nothing else!).

 

3. Create two stored procedures - one that SELECTs the comments, one that INSERTs them.

 

4. For the first one - just call it. For the second one: Filter the comments by using mysql_real_escape_string and strip_tags, than use a prepared statement with the entered comment as argument to execute the SQL command that EXECUTEs the second procedure.

 

 

That's about as safe as it will get, I think. PM me with anonymized code (no real Passwords or paths in it) if you've got detailed questions you don't want to share openly.

Edited May 15, 2009 by samm

[alanschu]

To be clear, I am just looking at possible pitfalls. I haven't actually done anything yet haha.

 

It is more my ignorance towards PHP I think. Just to be clear, if someone were to go to a PHP page and go "View Source" they will NOT see any of the actual PHP code for connecting to the DB or whatever?

 

 

EDIT: And yes, I was certainly only letting them have access to the one table with limited commands.

 

 

I'll probably have some questions for you in the near future. Step 4 is a bit hazy haha. My SQL experience has been as a part of a course where everyone in the group always had full privileges

 

 

But this is why I'm doing it. So I can get better with all this stuff haha.

Edited May 15, 2009 by alanschu

[samm]

Just to be clear, if someone were to go to a PHP page and go "View Source" they will NOT see any of the actual PHP code for connecting to the DB or whatever?

They will only see whatever the php script outputs. Puts out. Well, you know what I mean As an example, look at the source of this forum. No php tags visible

I'll probably have some questions for you in the near future. Step 4 is a bit hazy haha. My SQL experience has been as a part of a course where everyone in the group always had full privileges

Hehe, sure, maybe I can already offer some explanations on point 4 in advance (I'm bored / overworked, so I take my time):

 

If someone posts comments, they have to be caught in the PHP script, and assigned to a variable, like $comment. You can then use "$comment = strip_tags($comment)", which will delete all HTML-Tags from a comment, to prevent entering <script>-Tags and stuff. Or you can use htmlspecialchars to convert < to < etc., so it will be shown in the comment instead of removed, but it won't be interpreted by your browser. I assume that's what they do on these boards, as I could write <script> and it is still readable

 

Then you can use "$comment = mysql_real_escape_string($comment)", which escapes ", ' and the like to \", \' etc., so SQL injection attacks are more difficult. (When later on displaying comments containing escaped characters, you may have to use strip_slashes on them first to remove the escapes again.)

On preparing a statement for mySql: See here. I.e. you'd use

$stmt = $mysqli->prepare("CALL sp_insert_comment(?)")

$stmt->bind_param('s', $comment);

$stmt->execute();

$stmt->close();

 

I hope that will help you when you're implementing

Edited May 15, 2009 by samm

[alanschu]

Ah yes makes more sense when I look closely and see "Escape String" hehehe.

 

I'm sure I'll pester you more later if that is okay!

[samm]

Sure, no problem, I like to share stuff I (think I) know