MYSQL: inserting error

Question

MYSQL Problem inserting Certain type of Text

when i try to insert this for example:

INSERT INTO `Attacks_key` 
  (`Event_Key` ,`Variable` ,`Value` ,`Impact` ,`Tags`)
    VALUES 
  ('111', 'REQUEST', ' mysql_real_escape ', '222', 'xss, csrf, id, rfe, lfi');

its inserted But when I try to insert this :

INSERT INTO `Attacks_key` 
 (`Event_Key` ,`Variable` ,`Value` ,`Impact` ,`Tags`) 
   VALUES 
 ('111', 'REQUEST', 'mysql_real_escape_string($_POST['username']); ', '222', 'xss, csrf, id, rfe, lfi');

MYSQL display this message :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'username']); ', '222', 'xss, csrf, id, rfe, lfi')' at line 1

and this is my php code

$user="root";
$password="*";
$database="*";
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$sql = "SELECT `Key_id` FROM `Event` ORDER BY `Key_id` DESC LIMIT 1";
$result =mysql_query($sql);
$row = mysql_fetch_assoc($result);
$EventKey= $row['Key_id'];
$query="INSERT INTO `PHPLOGS`.`Attacks_key` (`Event_Key` ,`Variable` ,`Value` ,`Impact` ,`Tags`) VALUES ('$EventKey', '$getname', '$getvalue', '$getimpec', '$gettags');";
mysql_query($query);
mysql_close();

and the input 'mysql_real_escape_string($_POST['username']); from the user

Can someone help

thanks

Answer 1

Try

$query = sprintf("INSERT INTO Attacks_key (Event_Key ,Variable ,Value ,Impact ,Tags) VALUES ('111', 'REQUEST', '%s', '222', 'xss, csrf, id, rfe, lfi');", mysql_real_escape_string($_POST['username']));

There's a conflict of ' in that part of the statement with 'username'

Have a look at mysql_real_escape_string

Answer 2

As you should really really switch to PDO instead of the deprecated mysql_ functions you could use PDO::quote

PDO::quote() places quotes around the input string (if required) and escapes special characters within the input string, using a quoting style appropriate to the underlying driver.

However, the preferred way of doing queries is by using prepared queries, and in that case you wouldn't have the quote escaping problem anyways. Again quoting from the php manual page for PDO:;quote

If you are using this function to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.

If for whatever reason PDO isn't an option, there's mysqli_real_escape_string with a similar functionalty.

Answer 3

If you're inserting mysql_real_escape_string($_POST['username']); literally, then escape the single quotes.

INSERT INTO `Attacks_key` 
 (`Event_Key` ,`Variable` ,`Value` ,`Impact` ,`Tags`) 
   VALUES 
 ('111', 'REQUEST', 'mysql_real_escape_string($_POST[\'username\']); ', '222', 'xss, csrf, id, rfe, lfi');