utf8 text and braces (',")

[Former user]

Originally reported on Google Code with ID 75

What steps will reproduce the problem?
1. i made a db in well-known firefox sqlite plugin. it contains text fields like:
'Рога и копыта' or even w/o braces: Уточняйте у менеджера .
2. But it seems not to be able to operate with strings like these in phpliteadmin.
whenewer i try to change it, for example, to : 'Рога и копыта 2'
it gives me an error i mentioned below.
3. the only way to manipulate with these strings is to rewrite it in "" braces. like:
"Рога и копыта 2". but now i should reedit all my fields in DB to double braces ((.

What is the expected output? What do you see instead?
i see:
Warning: SQLite3::query() [sqlite3.query]: Unable to prepare statement: 1, near "Рога":
syntax error in <filepath> on line 669
i expected to see:
automatically change ' to " if thereare any utf symbols, or teach phpliteadmin to work
with ' braces with utf.

What version of the product are you using? On what operating system?
gentoo, define("VERSION", "1.9.0");

Please provide any additional information below.

utf text w/o any braces is able to be filled into a field in firefox sqlite plugin
too, but it need to be recontained in "" in phpliteadmin. 

Reported by remort.net on 2011-12-13 19:28:31

[Former user]

``` Annoying quotes. The problem is wider. If you want to insert a text string into text field, you must place it in quotes. "string". Otherwise you'l got an syntax error.

Ok. I popullated the table with some rows with text fields. All text fields in quotes. I wonder how it looks in sql for real and exported my table in sql. And what i've got:

INSERT INTO firms2 (id,name,adress,sro,schema,branch,ifns,uchred,state,rs,us_kap,price,dop_info,skidka,reg_date,add_date) VALUES (1,Рога и копыта,Уточняйте у менеджера,0,1,6,1,1,1,Открыт в банке Альфа-Банке,50000,100000<sub>,2011-12-15,2011-12-15);</sub>

INSERT INTO firms2 (id,name,adress,sro,schema,branch,ifns,uchred,state,rs,us_kap,price,dop_info,skidka,reg_date,add_date) VALUES (2,Дока и Ко,Уточняйте у менеджера,0,1,6,1,1,1<sub>50000,100000,Разработка документации</sub>2011-12-15,2011-12-15);

You can see - there aren't any quotes arount text fields data in VALUE statements. So, if you try to create a new table with this code - it fails... Because of text w/o quotes. It doesn't matter if you use utf-8 or ascii, i tried ascii too.

You can repeat this test in a few seconds. It's 100 % repeatable.

Finally, if phpliteadmin can't work with text w/o quotes - make it possible to quote any text automatically. For example: quote text while export, quote text while inserting a text field and so on... Presently i must place quotes manually for phpliteadmin everywhere i want to insert a text field. ```

Reported by `remort.net` on 2011-12-15 09:39:22

[Former user]

``` The bug should be renamed to smth. like 'phpliteadmin should place quotes around the text fields by itself. even while exporting.' ```

Reported by `remort.net` on 2011-12-15 09:42:05

[Dane Iracleous]

``` Which SQLite library is being used? PDO, SQLite3, or SQLiteDatabase? ```

Reported by `diracleo` on 2011-12-16 05:05:57

[Former user]

``` SQLite version: 3.7.4 SQLite extension [?]: SQLite3 PHP version: 5.3.6-pl0-gentoo

The database is of 3rd version of sqlite ```

Reported by `remort.net` on 2011-12-20 06:47:35

[Former user]

``` i patched this !!! once again: i use Sqlite3 database and phpliteadmin uses type=SQLite3 . Whenever i need to Create Row or Insert a new row i should plase every value in a text field in doublequotes ("). elsewhere it would be an error inserting a text field.

it's an annoying procedure and i dicided to find where liteadmin inserts a textfield and add quotes directly in code. i changed 2 strings (for inserting a new row and for editing existing row), but there are somemore 'actions' in phpliteadmin that insert a text fields (this actions need to be patched too in general).

so, here is the patch:

diff ./phpliteadmin.php ./phpliteadmin.php.backup 2012c2012 < $query .="\"".$db->quote($value)."\""; ---

$query .= $db->quote($value);

2116c2116 < $query .= "\"".$db->quote($_POST[$pks[$i].":".$fields[$j]])."\""; ---

$query .= $db->quote($_POST[$pks[$i].":".$fields[$j]]);

it just adds quotes to the POST vars. and it works for me.

i attached a screenshot of inserting/editing a row with textfields. first field 'name' is invalid and second 'adress' is ok, because of quotes. so i just automated a process of quotation.

magic quotes gps in my php.ini is off. and it swiched off in general in other servers now, cause it goes down, it would be deprecated in later versions of php. anyway change of this param. wasn't useful for this issue.

please check and test my dirty hack and fix this annoying issue in a proper way for your project. if you need more info - ask me!

```

Reported by `remort.net` on 2011-12-26 08:54:11

[Christopher Kramer]

``` I could reproduce this issue. It happens with SQLite3 and SQLite but not with PDO. This is because phpliteadmin uses PDO's quote() which adds quotes and escapes the string. SQLite3 and SQLite escapeString() only escape the string, so we'd need to add extra quotes.

Your patch will break PDO because then it adds too many quotes.

You need to adjust the quote() function like this:

correctly escape a string to be injected into an SQL query and put single quotes around it public function quote($value) { if($this->type=="PDO") { PDO quote() escapes and adds quotes return $this->db->quote($value); } else if($this->type=="SQLite3") { return "'".$this->db->escapeString($value)."'"; } else { return "'".sqlite_escape_string($value)."'"; } }

Please give this a try and tell me whether it works for you, especially with your UTF8 problems you mentioned at the beginning. ```

Reported by `crazy4chrissi` on 2012-03-28 12:31:52

[Former user]

``` to crazy4: Hi ! I downloaded your version of 1.9.1 with CSV import and use it with sqlite3 database (SQLite version: 3.7.7 , SQLite extension : PDO). In previous case i used sqliteV2.

Field inserts works perfect with quotes/unicode/special symbols. Great ! If i patch your version with my pieces : ."\"" . All the insertion breaks on sqlite 3 with PDO. So i'll use sqlite3 PDO now with your version.

But i can't import a CSV string (unknown Error). Which format of CSV your version should accept?

My email: master[at]remort.net , icq is 228072974. You can feel free to write me. It would be much faster then.

And thanks for quoting patch ! ```

Reported by `[e-mail]` on 2012-03-28 13:49:59

[Former user]

``` Shits ! I just find out that your CSV version doesn't contain quotation patch... Oh, ok. Now i see. I used "\"" right before quote() function right in middle of a code of a phpliteadmin when i used sqlitev2 databases, and it worked great for it, but broke sqlitev3 PDO. And you change the code for sqliteV2 just in quote() function itself. So it should work only for sqlitev2 and not touch strings if you work with sqliteV3. Now i understood.

I just don't have an php-sqlite v2 mod now, work with sqlitev3 PDO driver only, but i think it would work, cause it's like my patch, but works more gently.

Such a stupid me )) ```

Reported by `[e-mail]` on 2012-03-28 14:02:22

[Dane Iracleous]

``` The improved quote function by crazy4chrissi in comment 6 has been included in the most recent release, v1.9.2. Closing this issue... ```

Reported by `diracleo` on 2012-05-30 12:34:42 - Status changed: `Fixed`

[Christopher Kramer]

``` Issue 72 has been merged into this issue. ```

Reported by `crazy4chrissi` on 2012-05-30 14:42:25

[Christopher Kramer]

``` Issue 84 has been merged into this issue. ```

Reported by `crazy4chrissi` on 2012-05-30 14:45:40

[Former user]

``` Mine was faster :P ```

Reported by `nipten` on 2012-05-30 15:24:35

[Christopher Kramer]

``` Yes, you are right that you were faster. In fact you should get the kudos for reporting and fixing this.

I did not notice your issue when fixing this at the time. And now 1.9.2 was released with my fixes and I became part of the developer team. So now I am going through the bugtracker to get an overview of the issues and merging duplicates like this in the process. Maybe I should have merged issues the other way round, but I chose this issue to be the main one because it is the one that made it into the release.

Thanks a lot for your contribution, though! I hope I can avoid duplicate fixes of the same issue by having a better overview of the bugtracker. Let's see. ```

Reported by `crazy4chrissi` on 2012-05-30 15:34:54

[Former user]

``` I'm just happy this project is seeing some activity. It's very useful keep up the good work. ```

Reported by `nipten` on 2012-05-30 18:11:17

[Christopher Kramer]

Issue 73 has been merged into this issue.

Reported by crazy4chrissi on 2012-11-06 15:00:13

[Christopher Kramer]

Issue 103 has been merged into this issue.

Reported by crazy4chrissi on 2013-03-09 17:15:04

[Christopher Kramer]

Reported by crazy4chrissi on 2013-03-09 17:15:54