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 ?

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

I don't know the first thing about Joomla, but I'd expect it to have some database API that handles escaping in some fashion. What are you using to talk to the database? — deceze, May 07 '13 at 10:35
The framework-agnostic way to [prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) has been discussed many times. Joomla has a database class but you don't seem to be using it. — Álvaro González, May 07 '13 at 10:43
Seems you're supposed to use the `::quote()` method of the database class: http://docs.joomla.org/Accessing_the_database_using_JDatabase/3.1 — deceze, May 07 '13 at 10:45
@ÁlvaroG.Vicario I'm using standard way of accessing db in joomla according to this. http://docs.joomla.org/Accessing_the_database_using_JDatabase/2.5. But we have to filter our inputs our methods before querying. — ChamingaD, May 07 '13 at 10:46
@deceze If I use ::quote() i don't have to use mysql_real_escape_string ? — ChamingaD, May 07 '13 at 10:48
@ChamingaD The **Inserting a Record** section of your link seems to explain it: `$db->quote()`. — Álvaro González, May 07 '13 at 10:51
@ÁlvaroG.Vicario I don't think only quoting is enough. Will it escape harmful characters ? — ChamingaD, May 07 '13 at 11:10
http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#quote — deceze, May 07 '13 at 11:30
@deceze I was checking http://docs.joomla.org/Secure_coding_guidelines. They first quote() and use getEscaped() method. — ChamingaD, May 07 '13 at 11:41

score 12 · Accepted Answer · answered May 07 '13 at 13:03

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.

What about this? http://stackoverflow.com/questions/21476081/get-dynamic-post-fields-data-via-jinput-in-joomla — Mohd Abdul Mujib, Jan 31 '14 at 12:04

Álvaro González · Answer 2 · 2013-05-07T11:42:19.220

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.

Don't JDatabase::quote() does same as mysql_real_escape_string() ? I found this in Joomla framework code - public function escape($text, $extra = false) { $result = mysql_real_escape_string($text, $this->getConnection()); if ($extra) { $result = addcslashes($result, '%_'); } return $result; } — ChamingaD, May 07 '13 at 11:35
Whats the reason for using addcshashes() with mysql_real_escape_string() ? http://i.imgur.com/MGxQI3F.png — ChamingaD, May 07 '13 at 11:39
Honestly, I can't think of a valid reason. Looks like the action of some clueless developer. — Álvaro González, May 07 '13 at 11:46

Best way to prevent SQL injections in Joomla

2 Answers2

Why not handle this in one step?

Linked