Php – Best way to prevent SQL injections in Joomla

joomlamysqlphp

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'));

Best Solution

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.