Solved

Need PHP function for sql database

Posted on 2011-09-07
8
300 Views
Last Modified: 2012-05-12
Hi - I'm a newbie to PHP.
I need a function to access an sql database.
I'm using the following code which is very messy and probably unsafe, but definitely a waste as it's repeated so many times (but it does work):
Search.php -
  $db_name = "db.Questions";  
  include "connect.php";
  $sql = "select * from db.Questions where var0='".$Survey."' order by var3";
  $res = mysql_query($sql);
  $ct = mysql_num_rows($res);
  mysql_close();

and the include (connect.php) has -
<?php
  $ctr=0;
  $errmsg = "";
  $db_host = "localhost";
  $db_user = "user";
  $db_word = "word";
  $link = mysql_connect($db_host, $db_user, $db_word);

  if (!$link) {
    die('Could not connect: ' . mysql_error());
  }
  $db = "db";
  if (!mysql_select_db($db)) {
    die('Could not select database: ' . mysql_error());
  }
  mysql_select_db($db, $link);
?>  

How do I get it all into connect.php?
0
Comment
Question by:dcass
  • 6
  • 2
8 Comments
 
LVL 2

Expert Comment

by:shdwmage
ID: 36499964
Wow, what a mess!

Search.php - 
  $db_name = "db.Questions";   
  include "connect.php";
  $sql = "select * from db.Questions where var0='".$Survey."' order by var3";
  $res = mysql_query($sql);
  $ct = mysql_num_rows($res);
  mysql_close();

Open in new window


<?php
  $ctr=0;
  $errmsg = "";
  $db_host = "localhost"; 
  $db_user = "user";
  $db_word = "word";
  $link = mysql_connect($db_host, $db_user, $db_word);

  if (!$link) {
    die('Could not connect: ' . mysql_error());
  }
  $db = "db";
  if (!mysql_select_db($db)) {
    die('Could not select database: ' . mysql_error());
  }
  mysql_select_db($db, $link);
?>  

Open in new window


Sorry it's a lot easier to read in the code boxes as I can use highlighting.

So if I understand you correctly you just want to put all of this SQL in one file?
0
 
LVL 2

Expert Comment

by:shdwmage
ID: 36500013
I guess the best way to help you would be to know what exactly you are looking for as far as results.

When I make a database connection I use a db_connect file that I use as an include on each page.
<?php 
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'db');
DEFINE ('DB_USER', 'public');
DEFINE ('DB_PASSWORD', 'password');

//make the connection
$dbc = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die("Couldn't connect to MySQL");

//select the database
@mysql_select_db(DB_NAME) or die("Couldn't open $db: ".mysql_error());

?>

Open in new window


I always try to keep my connection information separate from my queries because I can re-use the file in many different applications with a few changes, like username and password.

But if you really wanted to merge them into connect.php all you would need to do is replace the include "connect.php" part in search.php with the code in connect.php

<?php
  $db_name = "db.Questions";   
$ctr=0;
  $errmsg = "";
  $db_host = "localhost"; 
  $db_user = "user";
  $db_word = "word";
  $link = mysql_connect($db_host, $db_user, $db_word);

  if (!$link) {
    die('Could not connect: ' . mysql_error());
  }
  $db = "db";
  if (!mysql_select_db($db)) {
    die('Could not select database: ' . mysql_error());
  }
  mysql_select_db($db, $link);
  $sql = "select * from db.Questions where var0='".$Survey."' order by var3";
  $res = mysql_query($sql);
  $ct = mysql_num_rows($res);
  mysql_close();
?>

Open in new window

0
 
LVL 2

Expert Comment

by:shdwmage
ID: 36500044
Additionally I would just like to explain what an include does.

When you use an include you are taking the content of the file you have written and merging all of the data of it into the current file.  

This allows you to "modularize" your code, making future updates and changes easier by allowing you to use the same chunk of code in many files.  It saves space on your server, and saves a lot of duplicate code to boot.
0
 

Author Comment

by:dcass
ID: 36500282
I do understand include files - I'm new to php but not asp - I've used that for years.
What I need is :
1) to make sure that my connection info is secure
2) to pass the info, including the sql statement, into a function and bring back the results
OR
is what I'm doing OK?
What is the standard way of accessing a database in PHP?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 2

Accepted Solution

by:
shdwmage earned 500 total points
ID: 36500319
I normally store the MySQL data below the public_html directory into a separate folder and use the file system to bring up the data.  It will prevent easy access to the information.

Additionally I have a function that I call clean_input() that will scrub all user input data for a whole bunch of crap.  I could probably add a lot more error validation to this but here is what I use:

#2) Clean input - verify that input data has no malicous intent.
function clean_input($alpha){
	#check and see if data has actually been input
	if (isset($alpha)){
		$cleaned = htmlentities($alpha);
		$cleaned = stripslashes($cleaned);
		$cleaned = addslashes($cleaned);
		$cleaned = trim($cleaned);
		$cleaned = strip_tags($cleaned);
	#if not set it to blank and ignore it.
	} else {
		$cleaned = NULL;
	}
	#return the data to the main page
	return $cleaned;
};

Open in new window

0
 
LVL 2

Expert Comment

by:shdwmage
ID: 36500325
I then just use that function to check all user input data.
0
 

Author Comment

by:dcass
ID: 36500389
So, just build a text file with the login info and pull it up when I need it?  Then do the work (select, update, etc.) in the actual php pgm?

Thanks for the cleaner - I needed that too!
0
 
LVL 2

Expert Comment

by:shdwmage
ID: 36501776
I normally use a php file, but txt or even an ini file would work as well.

For security purposes none of my web-accessible users have access to the delete features.  If I need to delete a record I hide it by setting a flag (field on it) and then I just filter based on that field and not show the things flagged for deletion.

Normally I will have 3 different sets of MySQL users with differing permissions.

User - one that can just access and view the data. This is handy for pages that load from the database, but you want to reduce the surface for a SQL attack

Power User - I use this for areas where I have decided to give people access to change the database.  They have permissions to update, insert, etc just not delete.

Administrator - this is the account I use to administer the database, it has all the permissions but it isn't tied anywhere to the web world.

Sometimes i will have a fourth account, a counter type account.
counter-  this account only has the insert permission and it will insert things into a database table like IP, session info, etc. This allows me to track data where otherwise I wouldn't the person to be able to put things into my database.

Now I actually have all of these in one file and use a switch statement to figure out which one of them to use.

switch ($permissions) {
   case user:
        DEFINE ('DB_USER', 'public');
        DEFINE ('DB_PASSWORD', 'password');
       break;
    case poweruser:
        define some stuff here
     break;
     default:
          define some stuff here
     break;
};

Finish with the rest of the information.

Open in new window


That allows me to put more than one chunk of code in the same file.  In the file that calls the connection file I would predefine $permissions with what user I wanted it to use.  In case I forgot to do it is why the default is set.  Which in mine is normally the user permissions.

That is just a way to avoid file bloating and keeping all of your database connection information in one file.

Hope that helps, and glad I could help you alleviate your issues!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now