Best way to prevent SQL injections in Joomla

Question

I take variables from POST method and query them on MySQL with Joomla 2.5.

What is the most secured method to use ? Currently I'm using JRequest::getVar with mysql_real_escape_string. Is it correct ?

1. $_POST with mysql_real_escape_string

   $password = mysql_real_escape_string($_POST["pwd"]));

2. JRequest::getVar with mysql_real_escape_string

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

3. JRequest::getVar

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

4. JInput

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

5. JInput with mysql_real_escape_string

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

Or something else ?

EDIT 1:

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

Here is my query 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: Framework 11.1 escape() method 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() use mysql_real_escape_string() Will it be safe to use as below ?

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

Answer 1

In Joomla!, you never directly access any of the superglobals. Also, you should alway distinguish incoming and outcoming data. Thus, to get the incoming value from the request, use

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

(JInput is the right choice; JRequest is deprecated and will be removed in the future.) Now you have a clean value to work with. It is prepared to be handled with PHP.

The next thing is to use the value in an SQL query (outgoing), you have to escape it properly.

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

In difference to $db->escape(), $db->quote() adds the quotes required by the underlying database engine.

Why not handle this in one step?

Well, you might at some point want another type of output, eg. within a view (even if password is not best for this example, I use it for consistency):

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

Therefor it is good practice always to keep escaping as close at possible to where it is needed. For incoming data this is immediately after the retrieval, for outgoing data immediately before sending/printing.

Answer 2

I think the question hides a few misconceptions so I'll elaborate a proper answer.

First of all, mysql_real_escape_string() is a function from the legacy mysql extension. As such:

• It's no longer maintained
• It'll trigger E_DEPRECATED warnings in PHP/5.5
• It'll no longer be available in future PHP releases

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

Additionally, you cannot use it if you are not using the deprecated legacy mysql extension. If you use PDO, MySQLi, ADODB or anything else, it's useless and it won't work. Needless to say, it won't work either if you are using SQLite, Oracle, SQL Server or PostgreSQL. All DB extensions have (or should have) an alternative tool.

Now, the Joomla framework provides its own database classes. You appear to be using version 2.5 and the escape function is JDatabase::quote(). That's how the feature works in Joomla. I don't really understand why you think it might be unreliable but, if you think so, you'd better drop the complete JDatabase and use something else. What you cannot do is to mix stuff from different extensions that aren't designed to work together.

Edit: I've grabbed Joomla 2.5 and had a look at the source code. The quote() function is a wrapper for escape(), which belongs to an abstract class, JDatabase, that implements an interface, JDatabaseInterface. 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, is quote() the same as mysql_real_escape_string()? Obviously not. Does it do the same? Yes.