sql injection

[baltar]

I am having a wamp issue so I can't try these out right now.  According to the book I'm learning php with, I can easily avoid injection attacks this way: 

 

$a= stripslashes($a);

$a= mysql_real_escape_string($a);

 

What concerns me is the repetition of the variable, $a.  Does it matter?  Intuitively, it should. 

 

$a changes. By the time $a hits mysql_real_escape_string it is slash-free.  So it is a totally different "value" but still contained in the original variable which may have had slashes...just has me concerned a bit.

 

I know PDOs are the best way. I'm not there yet, unfortunately.

Edited May 23, 2014 by baltar

[requinix]

I am having a wamp issue so I can't try these out right now.  According to the book I'm learning php with, I can easily avoid injection attacks this way: 

 

$a= stripslashes($a);

$a= mysql_real_escape_string($a);

Not the best method. How old is this book? It should be teaching you about PDO and mysqli, which offer (among other things) prepared statements, which completely sidestep the whole SQL injection issue.

 

The stripslashes() shouldn't be there at all, though. Make sure you're running PHP 5.4+.

 

What concerns me is the repetition of the variable, $a.  Does it matter?  Intuitively, it should.

It does not matter in the slightest.

 

$a changes. By the time $a hits mysql_real_escape_string it is slash-free.  So it is a totally different "value" but still contained in the original variable which may have had slashes...just has me concerned a bit.

So what? The old value that had slashes is gone because it was overwritten with one that doesn't have any.

[baltar]

I am using The Joy of PHP. The author does use mysqli on occasion.

 

How would you re write it? I hate to ask but there is always a different way of doing this....on stackoverflow everyone seems hellbent on showing me some hideously complicated way to do mysqli. It is pretty frustrating. I apologize, but needed to get it out.

 

Why shouldn't stripslashes() be there though?

Edited May 24, 2014 by baltar

[kicken]

... some hideously complicated way to do mysqli. It is pretty frustrating. I apologize, but needed to get it out.

Mysqli's API is not particularly friendly, especially when it comes to prepared statements and bound parameters, which is the ideal way to query with user-supplied information.

 

PDO has a much cleaner api, and would be used as such:

//Connect
$db = new PDO('mysql:host=localhost;dbname=yourdatabase', 'username', 'password', array(
   PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));

//Prepare the query.  Since you didn't show a query this is just generic example
$sql = 'SELECT SomeColumn FROM SomeTable WHERE SomeOtherColumn=?';
$statement = $db->prepare($sql);

//Execute the query with the user-supplied data.  
$statement->execute(array($a));

foreach ($statement as $row){
   //Process results
}

Why shouldn't stripslashes() be there though?

The purpose of the stripslashes is to undo the damage done by the magic_quotes_gpc setting. This setting has been removed so there is no reason to be trying to undo it. Even before the call should have been conditional, as in:

if (get_magic_quotes_gpc()){
 $a = stripslashes($a);
}

because running stripslashes if magic quotes was already off would at best do nothing, or at worst damage a user's input (ie, remove slashes they added intentionally).

[baltar]

Thanks for helping.  Apologies for being a complete dumbas* here.  I'm pretty much jumping like 10 steps in my development...so I am about to ask at least one more stupid question...please bear with me lol!  Where does the PDO go, exactly?  I think in the user's query (basically my 2nd set of code)?

 

Just to be sure, I essentially have two scripts (one that connects, creates, and populates a tiny db):

<?php 

/*Joy of PHP sample code*/
 
if (mysqli_connect_error()) 
{
die('Could not connect: ' . mysqli_connect_error());
}
echo 'Connected successfully to mySQL. ';
 
/* Create table doesn't return a resultset */
 
if ($mysqli->query("CREATE DATABASE Cars") === TRUE) 
{
echo "Database Cars created";
}
else
{
echo "Error creating Cars database:  " . $mysqli->error."<br>";
}

$mysqli->select_db("Cars");
Echo ("Selected the Cars database");

$query= " CREATE TABLE INVENTORY (VIN varchar(17) PRIMARY KEY, Comments varchar(150), a varchar(50), Model varchar(100))";
//echo "<p>*****</p>";
//echo $query;
//echo "<p>*****</p>";

if ($mysqli->query ($query) === TRUE)
{
echo "<p>Database table 'INVENTORY' CREATED</p>";
}
else
{
echo "<p> ERROR: </p>" . mysqli_error($mysqli);
}

$query = "INSERT INTO `cars` . `inventory` (`VIN`, `Comments`, `a`, `Model`) VALUES ('5FNYF4H91CB054036',  'Really \n good', 'Honda', 'Pilot')";
//a stands for the manufacturer of the car, in the book this variable is Make

if ($mysqli->query($query) === TRUE)
{
echo "<p>Honda Pilot inserted into inventory table. </p>";
}
else
{
echo "<p>Error inserting Honda Pilot:</p>" . mysqli_error($mysqli);
echo "<p>*****</p>";
echo $query;
echo "<p>*****</p>";
}

//Insert a Dodge Durango

$query= "INSERT INTO `cars` . `inventory` (`VIN`, `Comment`, `a`, `Model`) VALUES ('LAKSDFJ234LASKRF2', 'Also \n very \r\n good', 'Dodge', 'Durango')";

if ($mysqli->query($query) === TRUE)
{
echo "<p>Dodge Durango inserted into inventory table </p>";
}
else
{
echo "<p>Error inserting Dodge: </p>" . mysqli_error($mysqli);
echo "<p>*****</p>";
echo $query;
echo "<p>*****</p>";
}

$mysqli->close();
?>

Then I have my own php setup that allows visitors to search the the inventory table within the car database. This is where the PDO would go into I assume (but the $sql portion of the PDO throws me off):

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');
$search_output = "";
if(isset($_POST['searchquery']) && $_POST['searchquery'] != "")
{
$searchquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['searchquery']);

$sqlCommand = "SELECT VIN, Comments, a, Model FROM Inventory WHERE a LIKE '%$searchquery%'";

include_once("db_folder/db.php");
 $query = mysql_query($sqlCommand) or die($mysqli->error);
 $count = mysql_num_rows($query);
if($count > 0)
{
$search_output .= "$count result(s) for <strong>$searchquery</strong><br />";
while($row = mysql_fetch_array($query))
{
 $VIN = $row["id"];
 $a = $row["a"];  //a really stands for the manufacturer of the car
 $a= mysql_real_escape_string($a); //still a novice
 $Model = $row["Model"];
 

$search_output .= "*<br><b>$a</b>- <br/><b>The Model is: </b>$Model<br /> <br /> <b><i>Its VIN is: </i></b><i>$VIN</i><br /><b><i>The impression is: </i></b>$Comments<br/>";
   echo nl2br($Comments);
   //output $means with line breaks
 } // close while
} else {
$search_output = "<hr />0 results for <strong>$searchquery</strong><hr />$sqlCommand";
}
mysql_close();
}
?>

I've clearly cut all the additional code not pertaining to the database.  This 2nd php code is actually about 130 lines, but mostly page content, etc.  I'm usually not as slow as the current evidence is bearing (I have a pretty decent GPA in school).

Edited May 24, 2014 by baltar

[Psycho]

PDO is an alternative driver for working with databases. So, all the code you have now that uses "mysql_" functions would be replaced - in some fashion. It isn't going to be a drop-in replacement. You'll need to take a little time to learn how to use prepared statements which is one of the huge benefits of using PDO. The mysqli_ drivers have prepared statements as well, but aren't as easy (in my opinion). Plus, the PDO drivers are compatible with different types of databases - but just MySQL.

[baltar]

Thanks. Could you point me to some tutorials? Php.net and wiki.hashphp.org do the job conceptually i guess, but do you know of others?

 

I mean I buy books on php, but they contradict each other. Stackoverflow gives me dissertations that are way above my head.

 

It's just frustrating. I have a simple 3 to 4 variable database and an nl2br() function. It shouldn't be this difficult.

[baltar]

Ps as far as pointing me to tutorials go, I mean that in respect to the 2nd script I have.

 

I found plenty of tutorials that use DPOs to create the db. but SEARCHING is the more important component. Sorry for any confusion.

[Psycho]

http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059