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.