Best way to prevent SQL injections in Joomla

Question

I take variables from the POST method and query them in MySQL using Joomla 2.5.

What is the most secure method to use? I am currently using JRequest :: getVar with mysql_real_escape_string. Is it correct?

• $ _ POST with mysql_real_escape_string

  $ password = mysql_real_escape_string ($ _ POST ["pwd"]));

• JRequest :: getVar with mysql_real_escape_string

  $ password = mysql_real_escape_string (JRequest :: getVar ('pwd', '', 'post'));

• JRequest :: getVar

  $ password = JRequest :: getVar ('pwd', '', 'post');

• Jinput

  $ password = $ jinput-> get ('pwd', '', 'STRING');

• JInput with mysql_real_escape_string

  $ password = mysql_real_escape_string ($ jinput-> get ('pwd', '', 'STRING'));

Or something else?

EDIT 1:

I found another method that manages characters using mysql_real_escape_string http://docs.joomla.org/API15:JDatabaseMySQL/getEscaped

Here is my request code.

$db = JFactory::getDbo(); $query = $db->getQuery(true); $query->select(array('username', 'password', 'state','name')); $query->from('#__dbusers'); $query->where('username = \''.$loginUsername.'\' AND password = \''.$loginPassword.'\' AND state > -1'); $db->setQuery($query); $results = $db->loadObjectList(); 

EDIT 2: escape () method 11.1 for MySQL

 public function escape($text, $extra = false) { $result = mysql_real_escape_string($text, $this->getConnection()); if ($extra) { $result = addcslashes($result, '%_'); } return $result; } 

Since escape () uses mysql_real_escape_string (), will it be safe to use as shown below?

$ loginUsername = mysql_real_escape_string (JRequest :: getVar ('user', '', 'post', 'STRING'));

Answer 1

At Joomla !, you never access any of the superglobals. In addition, you should always distinguish between incoming and outgoing data. So, to get the input value from the query, use

 $password = $jinput->get('pwd', '', 'STRING'); 

( JInput is the right choice; JRequest deprecated and will be removed in the future). You now have net worth for the job. He is ready to work with PHP.

The next is to use the value in the SQL query (outbound), you need to avoid it.

 $query->where("username = " . $db->quote($loginUsername) . " AND password = " . $db->quote($loginPassword) . " AND state > -1"); 

Unlike $db->escape() , $db->quote() adds the quotes needed by the underlying database engine.

Why not handle it in one step?

Well, at some point you may need a different type of output, for example. in the view (even if the password is not suitable for this example, I use it for consistency):

 echo $this->escape($password); // applies html_specialchars in HTML views 

Therefore, you should always avoid as close an opportunity as possible, where necessary. For incoming data, this is immediately after extraction, for outgoing data immediately before sending / printing.

Answer 2

I think this question hides several misconceptions, so I will develop the correct answer.

First of all, mysql_real_escape_string () is a function from the legacy mysql extension. In this way:

• It is no longer supported.
• It will trigger E_DEPRECATED warnings in PHP / 5.5
• It will no longer be available in future releases of PHP.

And I'm not talking about the function, I'm talking about the whole extension.

In addition, you cannot use it unless you are using the legacy legacy mysql extension. If you use PDO, MySQLi, ADODB or something else, it is useless and it will not work. Of course, it will not work if you use SQLite, Oracle, SQL Server or PostgreSQL. All database extensions have (or should have) an alternative tool.

The Joomla framework now provides its own database classes. You seem to be using version 2.5 , and the escape function is JDatabase::quote() . This is how a function works in Joomla. I don’t understand why you think this may be unreliable, but if you think so, it is better to abandon the full JDatabase and use something else. What you cannot do is mix material from different extensions that are not meant to work together.

Edit: I grabbed Joomla 2.5 and looked at the source code. The quote() function is a wrapper for escape() , which belongs to the abstract JDatabase class, which implements the JDatabaseInterface interface. There are three implementations:

• JDatabaseMySQL

   /** * Method to escape a string for usage in an SQL statement. * * @param string $text The string to be escaped. * @param boolean $extra Optional parameter to provide extra escaping. * * @return string The escaped string. * * @since 11.1 */ public function escape($text, $extra = false) { $result = mysql_real_escape_string($text, $this->getConnection()); if ($extra) { $result = addcslashes($result, '%_'); } return $result; } 

• JDatabaseMySQLi

   /** * Method to escape a string for usage in an SQL statement. * * @param string $text The string to be escaped. * @param boolean $extra Optional parameter to provide extra escaping. * * @return string The escaped string. * * @since 11.1 */ public function escape($text, $extra = false) { $result = mysqli_real_escape_string($this->getConnection(), $text); if ($extra) { $result = addcslashes($result, '%_'); } return $result; } 

• JDatabaseSQLSrv

   /** * Method to escape a string for usage in an SQL statement. * * The escaping for MSSQL isn't handled in the driver though that would be nice. Because of this we need * to handle the escaping ourselves. * * @param string $text The string to be escaped. * @param boolean $extra Optional parameter to provide extra escaping. * * @return string The escaped string. * * @since 11.1 */ public function escape($text, $extra = false) { $result = addslashes($text); $result = str_replace("\'", "''", $result); $result = str_replace('\"', '"', $result); $result = str_replace('\\\/', '/', $result); $result = str_replace('\\\\', '\\', $result); if ($extra) { // We need the below str_replace since the search in sql server doesn't recognize _ character. $result = str_replace('_', '[_]', $result); } return $result; } 

So mysql_real_escape_string() quote() match mysql_real_escape_string() ? Obviously not. Does he do the same? Yes.