Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need PHP function for sql database

Posted on 2011-09-07
8
Medium Priority
?
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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
 
LVL 2

Accepted Solution

by:
shdwmage earned 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

604 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