-1

Regarding SQL statements, we are using old SQL statements that are prone to SQL injection at this company I'm working for (they're all computer programmers with not alot of web experience) and I am trying to get them and myself to move towards using prepared statements. I have read so much about it already, but it's still a bit unclear.

For example, we are using an include(/mnt/configdb.php) and then a db_connect(); in our PHP files to connect to each SQL DB through an ini file using SQL (not SQLi). I want to use better methods/statements perhaps mysqli_connect() that will be flexible when we need to change servers and update SQL info (in the event of a server move). Any idea on what would work best? mysqli_connect() vs mysql_connect or using a function call to connect to the ini file on server like I'm doing already?

For example, I would like to set it up so that I create one connect.php file and it has multiple database connections for each schema I need to access, then I just include that file and the sqli statements in the necessary PHP file that is sending all the variables to the SQL DB that it needs to connect to. What's the best way to go about this or how can I convert what I have already (below) and turn it into sqli and prepared statements? Should I even use SQLi?? Or just stick with SQL?

configdb on server:

function db_config_cables()
{
//Parse and store the db ini file, this will return an associative array
global $dbhost,  $dbuser,  $dbpass,  $dbname;
//Point to global ini file
$config_info = parse_ini_file('config.ini', true);
//Call specific db
$dbhost = $config_info['tablename_here']['db_host'];
$dbuser = $config_info['tablename_here']['db_user'];
$dbpass = $config_info['tablename_here']['db_pass'];
$dbname = $config_info['tablename_here']['db_name'];
}

accessdb file on server:

function db_connect()
{
global $dblink, $dbhost,  $dbuser,  $dbpass,  $dbname;
$dblink = @mysql_connect($dbhost,  $dbuser,  $dbpass) or die ('DB ERROR - Could not connect: '.mysql_error());
@mysql_select_db($dbname) or die ('DB Error - Could not select: '.mysql_error());
}
function close_db()
{
global $dblink;
@mysql_close($dblink) or die ('DB ERROR - Could not close: '.mysql_error());
}

PHP file used to post data to SQL database:

require("/mnt/library/configdb.php");        
require("/mnt/library/accessdb.php");

//Connect to SQL DB
db_config_cables();
db_connect();

$unsafe_variable = $_POST['unsafe_variable'];

mysql_query("INSERT INTO tablename (column_name,...) VALUES('$unsafe_variable',...)")
/* close connection */
close_db();
jflay
  • 495
  • 8
  • 30
  • "INI vs mysqli_connect" doesn't make much sense, since you need to connect to a database using some method like `mysqli_connect` independently of where the configuration comes from. You may store it in an .ini-file or YAML file or … – feeela May 03 '13 at 18:41

2 Answers2

2

First, I would recommend going to PDO, not mysqli.

Second, don't just leave vlnerable code hanging out there.. it's really easy to wrap unsafe vars in mysql_escape_string

Third, I highly recommend using a model mapper abstraction instead of direct sql statements. This will save you writing 95% of the queries in the first place. Also, you can write a code generator to generate your model/mapper code for you from your database. It may sound difficult, but it's actually super super easy.

Zak
  • 23,916
  • 10
  • 36
  • 65
  • not a problem. asked and answered in this SO question: http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons – Zak May 03 '13 at 18:27
  • Move the reason into the answer with an appropriate tie-in - it is easily lost in comments. – user2246674 May 03 '13 at 18:27
0

First of all I don't understand why your concern is such a quite negligible thing, while you are leaving your queries still in danger.

Next, I'd strongly suggest PDO instead of mysqli.

Now to your question.
I don't see much problem here at all.

Your current setup is flexible already. Just change DB credentials in /mnt/configdb.php when moving to new server - that's all.

So, what'd I did:

the same /mnt/configdb.php file which contains either credentials and the actual connection code (as I see no point in having a distinct connect function. If you are including credentials - you are going to connect as well). And then include_once() it wherever I need a db connection:

require("/mnt/library/configdb.php");        
$stm = $pdo->prepare("INSERT INTO tablename (column_name,...) VALUES(?,...)");
$srm->execute(array($_POST['unsafe_variable']));

that's all.

Community
  • 1
  • 1
Your Common Sense
  • 152,517
  • 33
  • 193
  • 313
  • 1
    The posted code contains the old non-mysqli code as the "old way example". The OP acknowledged the need for placeholders, even they are not shown. Also, why PDO over mysqli? – user2246674 May 03 '13 at 18:26
  • because PDO itself normalizes and escapes all strings when inserted in the correct way and supports mysqli (and bunch of other database types). It's configurable via it's connect parameter – Willy Pt May 03 '13 at 18:33
  • 1
    @WillyPt PDO has nothing to do with mysqli. The latter is not a database server but just driver for mysql DB – Your Common Sense May 03 '13 at 18:35
  • i'm already looking into using prepared statements for my "queries that are in danger" but I am trying to get a better understanding of how to set up my ini file and connect statements to set myself up for using prepared statements – jflay May 03 '13 at 18:55