Author |
Message |
Initrd.gz Seasoned Helper
Joined: Sep 18 2008 Posts: 134 Location: Over there ---> Offline
|
Posted: 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? |
|
Back to top |
|
|
Doc Flabby Server Help Squatter
Joined: Feb 26 2006 Posts: 636 Offline
|
Posted: 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. _________________ Rediscover online gaming. Get Subspace | STF The future...prehaps |
|
Back to top |
|
|
Cheese Wow Cheese is so helpful!
Joined: Mar 18 2007 Posts: 1017 Offline
|
Posted: 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 _________________ SSC Distension Owner
SSCU Trench Wars Developer |
|
Back to top |
|
|
Initrd.gz Seasoned Helper
Joined: Sep 18 2008 Posts: 134 Location: Over there ---> Offline
|
Posted: 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 |
|
Back to top |
|
|
D1st0rt Miss Directed Wannabe
Age:36 Gender: Joined: Aug 31 2003 Posts: 2247 Location: Blacksburg, VA Offline
|
Posted: 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)
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. _________________
|
|
Back to top |
|
|
JoWie Server Help Squatter
Gender: Joined: Feb 25 2004 Posts: 215 Offline
|
Posted: 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 |
|
Back to top |
|
|
|