How can I use class MySqlDrivers method EscapeSql?

R_J

I'm wondering why I cannot use that mehod like this:

$MySQLDriver = new Gdn_MySQLDriver();
return $MySQLDriver->EscapeSql($string);

I get that error:
Fatal error: Using $this when not in object context in /usr/share/nginx/html/vanilla/library/database/class.mysqldriver.php on line 53

x00

you should be initialising these driver directly anyway. What if they switch from MySQL to Postgres or SQL Light?

Use

Gdn::SQL->EscapeSql();

However I question why you are using this directly what is the query?

Linc

Use : to name a parameter, then pass it as the second parameter to Query():

$this->Query("update :_User set Admin = 1 where Email = :Email", $SqlArgs);

x00

you should be initialising these driver directly anyway. What if they switch from MySQL to Postgres or SQL Light?

Use

Gdn::SQL->EscapeSql();

However I question why you are using this directly what is the query?

R_J

Haven't thought it is that easy, but it works great!

It's not that I want to use that functionality, but I want to integrate a script that requires me to encapsulate some of its calls. One is an EscapeSql call.

Thanks for your answer!

R_J

EscapeSql isn't doing what I've expected it to do. It transforms "User.Name" to "GDN_User.Name" and so it changes "127.0.0.1" to "GDN_127.0.0.1"

You were right from the start, @x00: it makes no sense at all to call it manually!

x00

what is the query you have go so far? Show us your workings.

R_J

I'm adopting a plugin that needs something like esc_sql($string); from WordPress. I'm fine with addslashes() now

x00

No you should never use addslashes for sql

sql statements need to be escaped as a whole not just the values, the later is not considered safe for many years.

PDO uses prepare which is abstracted by the framework.

Linc

@R_J said:
I'm adopting a plugin that needs something like esc_sql($string); from WordPress. I'm fine with addslashes() now

It sounds like you are adopting a plugin that needs its SQL totally refactored, per @x00's notes.

R_J

Yeah - as if porting some poorly documented functions isn't hard enough

I've taken a look at the source and nearly all queries are "select * from table" and "select count(id) from table". The only critical part is a function that builds insert sqls and although it looks okay to me, it'll be really easy to replace...

x00

@R_J you are nor providing you workings so how do you expect us to help you?

R_J

Sorry, I don't wanted to be impolite. I'm trying to make BadBehavior (popular WP anti spam plugin) run with Vanilla. I had it up and running, but now I'm working on replacing that insert statement.

I didn't gave you "the big picture" because I want to try to solve the others (minor) problems by myself before I bother someone else. Only when I get stuck I post a question. But you are surely right: whithout further asking I would have accepted that "addslashes()", which is how the example implementation for MediaWiki solves "escaping".

But if you have a look at their code, I think you see that it should really be safe the way it is working.
Nevertheless I skipped requiring their mysql "library" and replaced the two enclosed functions with a structure() and a custom bb2_insert. To be honest: I'm not finished with that insert sql because it didn't wark as expected, but if I make my way through this on my own, I'll learn something and that's why I'm not giving to much details.

Rest asure: if you would sit next beside me I would not stop asking you questions! But this way I prefer to work on my problems alone as long as I can.

But thanks for being helpful whenever I ask, anyway!

x00

I only asked for that one query.

R_J

There is not one single query. It is a "universal" function which is for inserting data to the log table:

function bb2_insert($settings, $package, $key)
{
    if (!$settings['logging']) return "";
    $ip = bb2_db_escape($package['ip']);
    $date = bb2_db_date();
    $request_method = bb2_db_escape($package['request_method']);
    $request_uri = bb2_db_escape($package['request_uri']);
    $server_protocol = bb2_db_escape($package['server_protocol']);
    $user_agent = bb2_db_escape($package['user_agent']);
    $headers = "$request_method $request_uri $server_protocol\n";
    foreach ($package['headers'] as $h => $v) {
        $headers .= bb2_db_escape("$h: $v\n");
    }
    $request_entity = "";
    if (!strcasecmp($request_method, "POST")) {
        foreach ($package['request_entity'] as $h => $v) {
            $request_entity .= bb2_db_escape("$h: $v\n");
        }
    }
    return "INSERT INTO `" . bb2_db_escape($settings['log_table']) . "`
        (`ip`, `date`, `request_method`, `request_uri`, `server_protocol`, `http_headers`, `user_agent`, `request_entity`, `key`) VALUES
        ('$ip', '$date', '$request_method', '$request_uri', '$server_protocol', '$headers', '$user_agent', '$request_entity', '$key')";
}

I've already rewritten that for Gdn::Sql()->insert()

x00

If you are not manually constructing the insert statement, any sql escaping is done internally. It is only unconventional query were they would try to directly execute the insert statement, becuase they could not use a driver method, that this would be an issue.

so you could do

Gdn::Sql()->Insert('InsertTable', array('Colomn1'=>'Value1'...))

Escaping is done internally.

Note this is not to do with output validation. Output validation is dependent on context. Some system sanitised their content before they put in the database, Vanilla does this on output, for ease of editing.

However this unrelated to SQL exploits and escaping.

Linc

Use : to name a parameter, then pass it as the second parameter to Query():

$this->Query("update :_User set Admin = 1 where Email = :Email", $SqlArgs);

x00

That is useful, but for a normal insert you can just use ->Insert( and an array of column/values.

It is only if you need to hand roll, would I use ->Query(, which comes in handy sometimes.

Linc

@x00 said:
for a normal insert you can just use ->Insert( and an array of column/values.

I absolutely agree, I just assumed we were in full hand-roll mode since he was digging in the SQL model for things like EscapeSql().

We just had a scenario in the office yesterday where a coworker needed that and it reminded me of this discussion. He started with mysql_real_escape_string which, while fine in theory, requires a deprecated PHP module that doesn't exist on our cloud servers. So I wanted to make sure our full-PDO solution was known.

x00

Sure mysql_real_escape_string isn't PDO aware so it is not really secure anyway, because there is no easy way of informing the mysql_* function of the PDO instance like a mysql_* identifier. That approach does come up a lot for those who perhaps left PHP development around 4 and then maybe came back.

I gathered from R_Js workings that he could most likely write the query using Insert(, which uses that method internally, but your example is still useful info.