preventing SQL injection

Hey guys so i have a form where i am getting data from text fields that the user will enter his/her data into and then passing those data fields into variables and then passing them in a SQL query to be saved into the database being used.

I am trying to figure out how to use mysql_real_escape_string on any text input field to prevent sql injection and causing problems like when a user enters a last name like: O'leary the data is being saved like this: O\'leary i have tried to add the mysql_real_escape_string in my query where i am passing the variables that contain all the data from the text fields but so far it hasn't worked for me.

Can someone please tell me the correct way to use this function sot that way the data will be validated. I AM DOING THE mysql_real_escape_string in my SQL QUERY

here is my code that gets whatever the user enters from the text fields and then assigns them into variables. the sql INSERT statement can be found at the bottom of this code snipet:

<?php
// a class
class User
{
public $user, $pass, $fname, $lname, $role, $role2;
public $user_err, $pass_err, $name_err, $pass_err2, $pass_err3;

public function __construct( $post_array ) {
$this->user = $_POST['username'];
$this->pass = $_POST['password'];
$this->pass2 = $_POST['password2'];
$this->fname = $_POST['first_name'];
$this->lname = $_POST['last_name'];
$this->role = $_POST['role'];


$this->user_err = NULL;
$this->pass_err = NULL;
$this->pass_err2 = NULL;
$this->pass_err3 = NULL;
$this->name_err = NULL;
}

public function validate() {

// username isn't a duplicate
if( !$this->user ) {
$this->user_err = "Please specify username";
} else if( duplicate( $this->user ) ) {
$this->user_err = "That username is already in use";
}

// passwords match and are at least 6 chars
if( !$this->pass || strlen( $this->pass ) < 6 ) {
$this->pass_err = "Password must be at least 6 characters";
} else if( $this->pass != $this->pass2 ) {
$this->pass_err = "Passwords do not match";
}

//user forgot to provide a password
if( !$this->pass || !$this->pass2)
{
$this->pass_err2 = "Please provide a password";
}

//user forgot to provide a ROLE (doctor or nurse)
if( $this->role == 0)
{
$this->pass_err3 = "Please select one Role";
}

// first/last name aren't blank
if( !$this->fname || !$this->lname ) {
$this->name_err = "Please provide a first and last name";
}

return !$this->has_errors();
}

public function has_errors() {
return $this->user_err || $this->pass_err || $this->pass_err2 || $this->pass_err3 || $this->name_err;
}

public function insert()
{

$sql = "
INSERT INTO users
(username, f_name, l_name, role, pword)
VALUES ( '$this->user', mysql_real_escape_string('$this->fname'), mysql_real_escape_string('$this->lname'), '$this->role', aes_encrypt( 'The Secret Phrase', '$this->pass' ));";

//echo "<hr>DEBUG SQL: " . $sql . "<hr/>\n";
mysql_query( $sql ) or die( "Error( $sql): " . mysql_error() );

}
}

function duplicate( $username )
{
//function used to test for duplicate usernames
$sql = "SELECT user_id FROM users WHERE username = '$username'";

$result = mysql_query( $sql ) or die( "Error( $sql): " . mysql_error() );

return mysql_num_rows( $result ) > 0;
}

?>

I think you need to learn more about PHP.

You can't invoke a *FUNCTION* in the middle of a string literal.


$sql = "INSERT INTO users "
. " (username, f_name, l_name, role, pword) "
. " VALUES ( '$this->user', '" . mysql_real_escape_string('$this->fname') . "',"
. "'" . mysql_real_escape_string('$this->lname') . "','$this->role', "
. "aes_encrypt( 'The Secret Phrase', '$this->pass' ));";

Though why you wouldn't want to escape $this->user and $this->role mystifies me.

If they are numbers, then why do you have apostrophes around them?

And if they aren't numbers then why aren't they also escaped?

So i copied and used the sql query you wrote and i did a debug to see if it would work and it didn't it seems that it is either missing a quote or has one to many also it is showing that it still is not fixing the issue if the users last name has an apostrophe ??? It is actually passing the string:

DEBUG SQL: INSERT INTO users (username, f_name, l_name, role, pword) VALUES ( 'richard1', . mysql_real_escape_string('Richard') . , .mysql_real_escape_string('O\'King') . , '1',

This makes no sense.

Where did the \' in O'King come from if not from using mysql_real_escape_string???

And *YOUR* code would have put the mysql_real_escape_string in there in the SQL, but mine shouldn't, since non of them are between "..." marks!

And where did the apostrophes go that I put in there. For example, where did the one from "'," go???

Or for that matter, where is the one that is just "'" ????

Are you sure you are running my code and not what you had originally??

i copied exactly what you gave me and used it and when i check what is being saved into the DB for fname and lname it is always:

fname: $this->fname
lname: $this->lname

Yes because you are including quotes where they don't belong:
mysql_real_escape_string('$this->fname')
so it is converting what was a variable into a string so that string is what is being inserted and not the value in the variable.

remove the single quotes and use only:
mysql_real_escape_string($this->fname)

still better though is to remove the MRES entirely from your query and use it earlier in your script:

$this->user = mysql_real_escape_string($_POST['username']);

You should also look up the MYSQL_PDO extensions and learn how to use them instead.

guelphdad:
okay so i tried what you said and in my function where i make a variable to store the data the user enters i did this:

$this->fname = mysql_real_escape_string($_POST['first_name']);
$this->lname = mysql_real_escape_string($_POST['last_name']);

then in the function where i actually do the sql query i kept the '$this->fname', '$this->lname' but it still does not correct the problem of a user with the last name like O'King and saving it in the DB like this: O\'King i also tried removing the single quotes but it just gave me an error so i still dont know how to solve this problem i am having

YOU WANT IT TO SAVE IT AS O\'King !!!!!

MySQL recognizes \' as an *embedded* apostrophe!

If you had looked in the table after inserting O\'King, you would see that what is actually stored there *IS* O'King.

TRY IT.

YOU WANT IT TO SAVE IT AS O\'King !!!!!

MySQL recognizes \' as an *embedded* apostrophe!

If you had looked in the table after inserting O\'King, you would see that what is actually stored there *IS* O'King.

TRY IT.

Old Pedant:
Sorry, but when i check my table in my DB it DOES NOT show as O'King and if i try to echo it out on the actual web page it displays like O\'King, incorrectly. So im sorry if maybe i am misunderstanding the way mysql_real_string works but i thought that it would be used to take care of problems like that

I don't know what to tell you.

Look at this copy/paste from my session with MySQL:

mysql> create table demo( name varchar(30) );
Query OK, 0 rows affected (0.23 sec)

mysql> insert into demo (name) values('My name is O\'Brien');
Query OK, 1 row affected (0.18 sec)

mysql> insert into demo (name) values('I don\'t like it without the \'!');
Query OK, 1 row affected (0.03 sec)

mysql> select * from demo;
+--------------------------------+
| name |
+--------------------------------+
| My name is O'Brien |
| I don't like it without the '! |
+--------------------------------+
2 rows in set (0.00 sec)


So whether it is because of your PHP class or what, I don't know. But MySQL *clearly* correctly treats \' as an embedded '

And, yes, mysql_real_escape_string *should* convert ' to \'

Were I guessing, I'd guess that somehow you are calling mysql_real_escape_string *TWICE* on the same value. And then, indeed, you would get those results.

Because then ' would first become \' and then the second call to the function would convert the \' to \\\'

Look what happens to that with MySQL:

mysql> insert into demo (name) values('this is what \\\' becomes');
Query OK, 1 row affected (0.02 sec)

mysql> select * from demo;
+--------------------------------+
| name |
+--------------------------------+
| My name is O'Brien |
| I don't like it without the '! |
| this is what \' becomes |
+--------------------------------+
3 rows in set (0.00 sec)

Maybe your PHP class is *already* calling mysql_real_escape_string for you and you didn't know it?