I tried using mysql_real_escape_string() by putting the variable that is being checked within the brackets:

mysql_real_escape_string($username);
mysql_real_escape_string($password);

Is that how you use mysql_real_escape_string()? Does that actually stop MySQL injections? I also have PHP checks that ensure the user enters only alphanumeric characters into the form.

Is this enough security for the form?

Thanks,

It's really not ^^

Try adding a few more functions to that bunch, like
base64_encode() md5() similar to below:

PHP Code:

<?php
md5(serialize(base64_encode(mysql_real_escape_string($username)));
md5(serialize(base64_encode(mysql_real_escape_string($password)));
?>

It's a bit of an overkill (there is no point doing all that, then md5'ing it), but you get my point. For things such as usernames and passwords you only really need to compare them, not much else.

mysql_real_escape_string is enough for the sql part but you must be careful at others

Thanks guys

Best thing to do is try and hack your own script (Put something like (^%$%^&*()_)(*&^%$£"£$%^&*''''#[l;;;;::: into the script a see what happens).

The symbols were stored in the database, can you see why it didn't work? I used the following code:

 
$location=$_POST['location'];
 
$location=mysql_real_escape_string($location);

Drew,

Try:

PHP Code:

<?php
$location = stripslashes($_POST['location']);

$location = urlencode(mysql_real_escape_string($location));
$location = serialize($location);
?>

The stripslashes( http://uk2.php.net/manual/en/function.stripslashes.php ) takes away the slashes, but then mysql_real_escape_string essentially puts them back.

The serialize( http://uk2.php.net/manual/en/function.serialize.php ) function is good when it's storing data. The urlencode( http://uk2.php.net/manual/en/function.urlencode.php ) should make the code URL friendly, thus making it a bit safer.

What exactly are you getting stuck on? What are you putting into the database and what were you expecting?

I want to allow users to enter their location in their profile but I don't want to allow symbols or MySQL queries.

I entered the following: Bristol^&*^

using the following code:

 
$location = stripslashes($_POST['location']);
$location = serialize($location);

And the output stored in the database was:

s:11:"Bristol^&*^";

Your explanations are good but I still don't know how to prevent MySQl injection attacks other than use mysql_real_escape_string() What exactly do I need to stop MySQL code entered by a user executing?

Thanks,

Originally Posted by drew22299

Your explanations are good but I still don't know how to prevent MySQl injection attacks other than use mysql_real_escape_string() What exactly do I need to stop MySQL code entered by a user executing?

A quick Google search will acquaint you a variety of possible SQL injection tactics. Basically you should do as much filtering/coercion of all user input as possible before using it within a SQL query.

Quickie example. Imagine your code is set up like this:

PHP Code:

<?php
$id_to_search=$_POST['id'];
$sql = "SELECT name FROM user WHERE id=$id_to_search";
foreach($db->query($sql) as $row) {
    echo $row['name']."<br>";
}
?>

Now if the $_POST['id'] was submitted as "1 OR 1=1", then all rows would be returned.

In this simple example, mysql_real_escape_string wouldn't stop this type of trickery since there aren't any quotes or other symbols being escaped. But you could easily prevent the attack by forcing $id_to_search to be an integer by changing its line to

PHP Code:

$id_to_search = (int) $_POST['id']; 


But again, please acquaint yourself with various SQL injections and try to hack your own code.

Might want to try searching the forums as this is a question which has been beat to death. See http://www.webmaster-talk.com/php-fo...njections.html for example.

I found this tutorial http://www.webmaster-talk.com/php-fo...injection.html

It explained sql injections really well, and you were right Jeremy there have been alot of previous posts about sql injections!

I think I understand what to do now, going by Rogem002's suggestions of using the following code:

stripslashes();urlencode();
mysql_real_escape_string();serialize();

Am I right in thinking the above code takes away any slashes in the user input, and adds % to the user data to make it more secure? And when I want to display the data stored in the database to the user, I have to use a function to remove the symbols that were added?

Drew,

Still having problems with this and making little progress and I know all of these things are small errors and probably seem obvious to most PHP programmers.

I'm guessing I need to use stripslashes when I output data from the database that has previously been run through mysql_real_escape_string(); when the user submitted the data, but I don't know how to do it for this:

$row=mysql_fetch_object(mysql_query("select * from users where userid='$_SESSION[userid]'"));

<tr bgcolor='#f1f1f1'><td ><font face='Verdana' size='2' >&nbsp;Email</td>
<td >stripslashes($row->email)</td></tr>

Didn't work

<tr bgcolor='#f1f1f1'><td ><font face='Verdana' size='2' >&nbsp;Email</td>
<td  >echo "stripslashes($row->email)";</td></tr>

Didn't work

Wouldn't it be easier if I used a check when the user submitted the data so they can't enter the symbols? For example,

if (!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)){
$submit= "no";}

One of the most effective way to stop them is to use binded query too.
This is what we call "dynamic sql" in the DBA world:

PHP Code:

<?php
$id_to_search=$_POST['id'];
$sql = "SELECT name FROM user WHERE id=$id_to_search";
foreach($db->query($sql) as $row) {
    echo $row['name']."<br>";
}
?>

For mysql, you must use the mysqli library (mysql improved)
http://www.php.net/manual/en/book.mysqli.php
A binded (or prepared) statement would be like this:

PHP Code:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");   //instantiate a mysqli ressource to the DB
(int)$id_to_search=trim(addslashes($_POST['id']));  //If php5 only, the (int) at the beginning indicates that the variable should only holds an integer, nothing else. the trim+addslashes is always good on user inputs, even if not the most indicated here

if (ctype_digit($id_to_search)!==true){   //ctype_digit check that only numeric values are in the variable. Even a 1.5 value will fail, as the dot is  not numeric
  die("The variable 'id' had alphanumeric character:".print_r($id_to_search,1));
}

$statement = $mysqli->prepare("SELECT name, id FROM user WHERE id=?");  //prepare the query
$statement->bind_param('i',$id_to_search);  //says that $id_to_search is the 1st parameter, and type string (the 's' at the beginning)
//if you have several variables, replace each of them by a ? in the prepare(), and call the bind_param() with all the mapping
//$id=5;
//$username="alfred";
//$city="gotham";
//$statement=mysqli->prepare("select x, y, z friom table where id=? and username=? and city like('?%')");
//$statement->bind_param('iss',$id_to_search, $username, $city);

$statement->bind_result($name,$id); //tells to which php variable should be tied the select column, in the order the column are specified
$statement->execute();   //Run the query
while($statement->fetch()){
  //Here, you get the $name and $id variables with the result from the select
  //Do what you want of them
}
$mysqli->close();  //close the db connection. Usually not necessary. 


This method is more complicated (in some aspects), but has several advantages:
1) You check in a strict manner the type of the arguments
2) The request is not rewrote each time, and the DB query planner can reuse the query plan, which means less work to the server. On a busy server, this can save you !!!
3) Well, it's simply more professional. But needs more typing.

That sounds useful, but I'm having trouble with the basics at the moment.

Like how to use stripslashes() when outputting in an html table, (detailed in my last post in this thread)

Or whether it would be easier or just as good to check the user input and if it contains symbols or slashes etc don't store the data to the database and display an error message telling the user to enter a value that doesn't include slashes etc

yes, and it's precisely what the (int) and ctype_digit() are doing.
They check that what the user gave you are really numeric (integer, to be 100% correct in that case).

NB:
An integer is a fully rounded number; 1, 2, 3 are integer numerics
A float value is a numeric value, with a floating precision: 1.562, 3.1418 are float numerics

(int) is what is called a hint, and has been introduced in PHP5.
ctype_digit() is present since php 4.0x (4.04, I believe...)

Use them if you need to verify that the content is really what it should be.

Originally Posted by tripy

This method is more complicated (in some aspects), but has several advantages:
1) You check in a strict manner the type of the arguments
2) The request is not rewrote each time, and the DB query planner can reuse the query plan, which means less work to the server. On a busy server, this can save you !!!
3) Well, it's simply more professional. But needs more typing.

We've been doing this for many years in the Microsoft world. Since .NET came out in late 1999, it's been considered (not just by programming purists, but by the people who sign our pay checks) to use dynamic SQL. It can't achieve the same performance (mainly for Tripy's point #2) and it opens up a host of security issues.

Select Username, PassHash From Users Where UserID = 17
Select Username, PassHash From Users Where UserID = 21

These are different queries. The execution plan should be identical (ideally an index seek) in both cases, but because the queries themselves are different, a database engine (MySQL, Oracle, SQL Server) will have to compile both queries, even if the first one's plan is sitting in cache. If instead you parameterize the query, its plan can be reused subsequently.

For a small SQL query, the compile time can be as long as the execution time. If it's being called in a loop by the application, this is what we in America call "sandbagging" performance.

Plus, if you use parameters, they're sent a binary data instead of text intermingled with code. This is a major difference that makes SQL Injection impossible even if you don't escape anything.

1) Once mysql_real_escape() has been used and it has added slashes to the user input, do I use stripslashses() when displaying the data from the database?

2) If the above is true, how can I use stripslashes within a HTML table, for example: The following doesn't work, what is the correct way?

<tr bgcolor='#f1f1f1'><td ><font face='Verdana' size='2' >&nbsp;Email</td>
<td  >echo "stripslashes($row->email)";</td></tr>

3) I have tested login box putting ' and ' or 1 etc and it doesn't recognise the username / password in the database, does this mean I am not testing it correctly or is the mysql_real_esacape() actually doing what it is supposed to do?

1) Yes, otherwise a string like

l'escargot n'avançait plus

(a simple french sentence) would be outputted as

l\'escargot n\'avançait plus

2)

PHP Code:

<tr bgcolor="#f1f1f1">
  <td >
    <font face='Verdana' size='2' >&nbsp;Email
  </td>
  <td  >
    <?php echo "stripslashes($row->email)"; ?>
  </td>
</tr>

You forgot the <?php and ?> between the PHP statement.
Or, if this table segment is in an PHP echo() call:

PHP Code:

$html='<tr bgcolor="#f1f1f1">
  <td >
    <font face="Verdana" size="2" >&nbsp;Email
  </td>
  <td  >
    '.stripslashes($row->email).'
  </td>
</tr>';
echo $html; 


Otherwise, it should be ok.

What seems to work best for me is something along the lines of

PHP Code:

$input = mysql_real_escape_string(stripslashes($_POST['input'])); 


That seems to easily stop the couple visitors that iv had that have tried to do sql injections, and I myself have been unable to do an injection

Just a note that you do not need addslashes. Addslashes is just about completely useless when it comes to preventing SQL injection with MySQL, and it is superfluous when used in addition to mysql_real_escape_string.

When you find the need to use stripslashes (i.e., all your data has a bunch of extra slashes), it means that the php.ini option 'magic_quotes_gpc' is enabled. This "security" feature basically just addslashes all incoming data; the thought was that newcomers wouldn't need to worry about SQL injection (they were wrong, of course). The option will be removed in PHP6.

Most web programmers have a bit of code in their "global.php" file that basically undoes this option. In addition of making the code more portable (you don't need to worry about if the option is on/off), it makes the data "clean", so no need for stripslashes anywhere. Here's an example:

PHP Code:

// Pretend you have a function array_stripslashes ...

if (get_magic_quotes_gpc()) {
    $_REQUEST = array_stripslashes($_REQUEST);
    $_GET     = array_stripslashes($_GET);
    $_POST    = array_stripslashes($_POST);
    $_COOKIE  = array_stripslashes($_COOKIE);
}

set_magic_quotes_runtime(0);