[mythtv] escaping strings in sql queries

Philippe C.Cattin cattin at vision.ee.ethz.ch
Mon Dec 22 04:56:37 EST 2003


Jost,

I did exactly this for mythbrowser last night.
I was working on the very same problem for mythbrowser last night. I 
implemented a mythbrowser specific solution, although I prefer a global 
function to do it.

what I found out so far is, that the single quot ', the % and _ need to 
be escaped (the double quot " seems to work fine without escaping).

regards, Philippe

> I'm a rather new mythtv user and encountered a problem with not properly 
> escaped sql queries, which I'd like to fix. It occurs since I switched to a 
> different xmltv provider (the german grabber by Ben Bucksch); the problem is 
> about quotes in titles. 
> 
> When I started to fix this, I noticed that there is some work already being 
> done to escape quotes in some places, e.g. in programinfo.cpp, which is why I  
> first wanted to ask if I missed something, before I start reviewing all sql 
> statements. 
> 
> The current code seems only to escape quotes and not other special characters 
> and it seems the quote escaping is still not enough, as I get things like 
> this:
> 
> 2003-12-21 22:24:34 Strange, file: 
> /var/store/21_20031221092000_20031221100000.nuv doesn't exist.
> DB Error (Recorded program deletion):
> Query was:
> DELETE FROM recorded WHERE chanid = 21 AND title = "Jim Knopf und die "Wilde 
> 13"" AND starttime = 20031220092700 AND endtime = 20031220100000;
> Driver error was [2/1064]:
> QMYSQL3: Unable to execute query
> Database error was:
> You have an error in your SQL syntax near 'Wilde 13"" AND starttime = 
> 20031220092700 AND endtime = 20031220100000' at line 1
> 
> The corresponding line in the XMLTV input is this:
> 
> <title lang="de">Jim Knopf und die &quot;Wilde 13&quot;</title>
> 
> This also led to error messages in the first run of mythfilldatabase after 
> changing to the new grabber.
> So my questions are:
> - is this a bug in myth or in the grabber (for using those &quot; entities)
> - is it okay if I try to ensure proper escaping of ascii string in all SQL 
> queries and send you a patch?
> 
> So far I added an escapeString(const QString) function to libmyth/util.* and 
> started a little work on changing the queries to use this function.
> 
> Thanks a lot,
> -Jost.
> 
> 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev


-- 
Dr. Philippe C. Cattin                          cattin at vision.ee.ethz.ch
Swiss Federal Institute of Technology, ETHZ         Tel: +41-1-632 25 29
Computer Vision Laboratory, CH-8092 Zuerich         Fax: +41-1-632 11 99




More information about the mythtv-dev mailing list