Server Help

Non-Subspace Related Coding - Sqlite String Escaping

Initrd.gz - Mon Apr 20, 2009 9:13 pm
Post subject: Sqlite String Escaping
Hello all

I am using the official C Sqlite3 Library. It does not appear that it has a character sequence escape function. I heard the only thing that needed to be escaped were quotes, but I am not that sure. Any way to do it?
Doc Flabby - Tue Apr 21, 2009 5:59 am
Post subject:
You should use parameterised queries eg.

sql like this

"Insert INTO table(c1,c2,c3) VALUES (?,?,?)"

then bind the parameters with

http://www.sqlite.org/c3ref/bind_blob.html

This eliminates the need to escape the strings and also has better performance.
Cheese - Tue Apr 21, 2009 11:32 pm
Post subject:
while we are here,
whats the difference in php between the
magic_quotes()
and
mysqlrealescapestring()
functions?

ive always assumed that theyre the same, just the 2nd uses a connection to the db somehow... :S
Initrd.gz - Wed Apr 22, 2009 5:04 pm
Post subject:
Mmk, now sqlite3 is simply not working.

sqlite.h includes fine, but none of the objects/functions are defined, even though I can look at it and see all the functions...

Main topic here: http://ubuntuforums.org/showthread.php?p=7120952#post7120952

Doc Flabby wrote:
You should use parameterised queries eg.

sql like this

"Insert INTO table(c1,c2,c3) VALUES (?,?,?)"

then bind the parameters with

http://www.sqlite.org/c3ref/bind_blob.html

This eliminates the need to escape the strings and also has better performance.

Unfortunately, I cannot do this. I am replacing the mysql database module in ASSS, and if I were to do that, all modules that use it would have to be rewritten, not to mention user input. PS I am using sqlite3_get_table
D1st0rt - Fri Apr 24, 2009 12:50 pm
Post subject:
Why can't you just do something like this for whatever modules need database access (not tested, but this should be the general idea)

Code: Show/Hide
import asss
from pysqlite2 import dbapi2 as sqlite3

class ModuleDataLayer:
    self.iid = asss.I_MODULE_DB

    def Open(self):
        self.conn = sqlite3.connect('/some/database.db')

    def GetSomeString(self, id):
        c = self.conn.cursor()
        c.execute('SELECT some_string FROM some_table WHERE id = ?' id)
        s = c.fetchone()
        c.close()
        return (s,)

    def InsertSomeData(self, text, number):
        c = self.conn.cursor()
        c.execute('INSERT INTO some_table (some_string, foreign_key) VALUES (?, ?)', (text, number))
        conn.commit()
        c.close()

    def Close(self):
        self.conn.close()


def mm_attach(a):
    a.module_db = ModuleDataLayer()
    a.module_db_int = asss.reg_interface(a.module_db, a)

def mm_detach(a):
    try:
        delattr(a, 'module_db')
        delattr(a, 'module_db_int')
    except:
        pass


You could do something similar for whatever database engines you have available if there is a python binding.
JoWie - Sat Jan 15, 2011 9:37 am
Post subject:
Cheese wrote:
while we are here,
whats the difference in php between the
magic_quotes()
and
mysqlrealescapestring()
functions?

ive always assumed that theyre the same, just the 2nd uses a connection to the db somehow... :S


Because using addslashes instead of mysql_real_escape_string allows for sql injections under certain character encodings. Example: http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string
There are a few cases where mysql_real_escape_string is vulnerable too.
Prepared statements are not vulnerable unless it has to emulate them in old mysql versions. http://php.net/manual/en/mysqli-stmt.bind-param.php
All times are -5 GMT
View topic
Powered by phpBB 2.0 .0.11 © 2001 phpBB Group