Basic SQL Injection Protection

[rofl90]

How do I protect against sql injections, using a basic form, I've heard of a few functions magic quotes, htmlentities, but which one sdo I use and in what context?

[pocobueno1388]

ALWAYS use mysql_real_escape_string() on all variables used in a query.

[rofl90]

in what context?

[ohdang888]

i've got a question also...

 

the link you provided shows this example on the page

<?

$query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",
                    mysql_real_escape_string($product_name, $link),
                    mysql_real_escape_string($product_description, $link),
                    $_POST['user_id']);

        mysql_query($query, $link);
?>

what is the %s and %d mean?????

[Northern Flame]

every time you use the function

mysql_query()

 

protect the data that the users input by using

mysql_real_escape_string()

 

for example:

 

$data = $_POST['data'];

 

$protected_data = mysql_real_escape_string($data);

 

$query = mysql_query("SELECT * FROM table WHERE data = '$protected_data'")or die(mysql_error());

 

while($row = mysql_fetch_array($query)){

echo $row['data'];

}

 

 

[ohdang888]

ohhh alright. thanks.

 

 

gosh i love this forum!

[rofl90]

wait would it alter a four letter, two number pw, in lower caps, md5'd and a 7 letter username with 1 capital letter at the start?

[tomfmason]

How do I protect against sql injections, using a basic form, I've heard of a few functions magic quotes, htmlentities, but which one sdo I use and in what context?

 

It should be noted the magic quotes and addslashes are not enough to stop all forms of sql injections. You should use the escape method for the database platform that you are using e.g. mysql_real_escape_string. You may also want to read some of the comments in addslashes.

[mainewoods]

ALWAYS use mysql_real_escape_string() on all variables used in a query.

--unless the variable is a numeric type variable and the corresponding db field is numeric meaning it will be used in the sql without single quotes.  mysql_real_escape_string()  will not protect against certain attacks in this case:

 

// user was supposed to enter just a number but entered the value below
// the value may also have come from a url passed var that was hacked
$userenterednumber = '1 OR 1';
// the statement below will have no effect because the 'OR' will not be escaped
$userenterednumber = mysql_real_escape_string($userenterednumber);
$sql = "SELECT * FROM usertable where username = 'user' and something = $userenterednumber";
// which would become:
$sql = "SELECT * FROM usertable where username = 'user' and something = 1 OR 1";
// I could get everybodies records! the test for username is completely bypassed. 

--the way to stop that is to use the settype function to clean numeric values for an sql statement:

// the variable '$userenterednumber' will be converted to a pure integer and then will be sql safe
settype($userenterednumber,  'integer');

 

[Northern Flame]

$sql = "SELECT * FROM usertable where username = 'user' and something = $userenterednumber";
// which would become:
$sql = "SELECT * FROM usertable where username = 'user' and something = 1 OR 1";

 

not if you changed the first variable to this:

 

$sql = "SELECT * FROM usertable where username = 'user' and something = '$userenterednumber'";

 

that will become

 

$sql = "SELECT * FROM usertable where username = 'user' and something = '1 OR 1'";

[mainewoods]

if the db field is defined as a numeric type field like integer, then putting single quotes around it in the sql statement will cause an sql error. You do not have a choice with numeric fields, the single quotes have to be left off.

[ohdang888]

should i use

mysql_real_escape_string()

 

for information drawn from a session?

 

edit:

ALSO, the url would be something like

 

test.php?blah=40

 

and then i get put the "40" in a variable. Do i ned to use the escape string on that too even though it won't go into my database?

[pocobueno1388]

and then i get put the "40" in a variable. Do i ned to use the escape string on that too even though it won't go into my database?

 

No, only when your dealing with the database. If your using that "40" in a select statement, then yes, use the function on it.

 

should i use

mysql_real_escape_string()

 

for information drawn from a session?

 

If your going to use it in a query, I would use it just in case. Better safe than sorry.