World: r3wp

I just found that there is a serious bug in sql-escape functions 
which is part of mysql-protocol...  new versions of MySQL for example 
autoconvert "'" to ' which MUST be escaped or you will get error 
or your query may be injected! Current sql-escape function do not 
escape such a cases.

insert db "create table esctest (text TINYTEXT)"
insert db "insert into esctest values ('a')" ;correct

insert db "insert into esctest values ('a'')" ;=== User Error: 
ERROR 1064

hm... now I see there is some discussion above about injection issues.. 
maybe I'm older version of mysql and this issue is already fixed

now... it's an issue with the latest version as well!

(now = no, I'm = I've....etc.:-)

hmm.. so it's not so bad.. the problem is just with semicolon:

insert db {insert into esctest values ("a;")} ;=== User Error: ERROR 
1064

no... my mistake.. I'm just still using old version... the new one 
is fine:]

:-) so, does the driver need to be fixed for "&#039" sequences or 
is it safe let them passed untouched to the server ?

it's safe

I have still some problems with semicolons:-(

and are you sure you're using latest version? ;o)

Version: 1.1.2

Now I'm sure there is something wrong in insert-all-queries function 
as it divides my query where is #";" (but inside data)

and now I have a prove:

not-squote: complement charset "'"
not-dquote: complement charset {"}
ws: charset " ^-^M^/"

data: {INSERT INTO hmm_kalendar_akce VALUES (NULL,9,'posledni-skotsky-kral',NULL,'(Last 
King of Scotland, The)','Poslední skotský král',NULL,NULL,'','http://www.kinoartbrno.cz/?stranka=film&film=posledni-skotsky-kral',0,NULL)}

;the parse part from insert-all-queries function:
	parse/all s: data [
		any [
			#"'" some ["\'" | "''" | not-squote] #"'"
			|{"} some [{\"} | {""} | not-dquote] {"}
			| #"`" thru #"`"
			| e: #";" (
				probe "next query?"
				probe e
			  ) any [ws] s:
			| skip
		]
	]

the problem is with the empty column in the query

there should be:
	#"'" any ["\'" | "''" | not-squote] #"'"
	|{"} any [{\"} | {""} | not-dquote] {"}

Thanks for the report, I'm adding your fix in the driver source.

New MySQL release 1.1.3 beta : http://softinnov.org/tmp/mysql-protocol.r

	o Fix for parsing correctly quoted empty strings. (Fix by Oldes)
		

 o Fixed an issue in case of automatic reconnection, the driver looses 
 local
	  flags like 'flat, auto-ping?, etc...(thanks to Will Arp)


 o Fix a remanence issue on /flat and /raw flags in 'send-sql after
	  automatic reconnection. (thanks to Will Arp)


 o Improved port recovery support after a failed request upon a shutdown 
 server.
	  Now the port will resume working once the server is up again.


 o An init SQL string can now be specified for an opened port. That 
 init string

   will be executed after automatic reconnections. (Useful to set, e.g., 
   a
	  specific charset value). Example : 

		  db-port/locals/init: "SET NAMES 'latin2'"

This new beta fixes mostly auto-reconnection bugs or issues. Please 
test it and report any bugs in CureCode bugtracker and issues here.

MySQL driver bugtracker : http://softinnov.org:8000/curecode/project.rsp?id=5

How to connect to DB when I need to use sock ( for example: localhost:/home/hmm/mysql/mysql.sock 
)

hm... already found how to get the correct port number so now it's 
not important

New MySQL release 1.2.0 beta : http://softinnov.org/tmp/mysql-protocol.r

o Changed behaviour and syntax of the 'read command :

	read mysql://[root-:-localhost]/
	==> return the list of databases

	read mysql://[root-:-localhost]/db
	==> return the list of tables in database "db"

	read mysql://[root-:-localhost]/db/tbl
	==> return the description of table "tbl" in database "db"

	read/custom mysql://[root-:-localhost]/db ["...sql query..."]

 ==> execute the sql query on database "db" and return the resultset.

This new 'read implementation avoids the SQL encoding issues encountered 
in previous versions. The side effect it that it will break compatibility 
with previous sources using 'read.

Thank you Dock!

Congrats Doc.

MySQL driver release v.1.2.0 - Download at : http://rebol.softinnov.org/mysql
- Changed behaviour and syntax of the READ function.
- Fix for parsing correctly quoted empty strings. (Fix by Oldes)

- Fix for the driver loosing local flags, like 'flat, 'auto-ping?,... 
when reconnecting (thanks to Will Arp)

- Fix a remanence issue on /flat and /raw flags in 'send-sql after 
automatic reconnection. (thanks to Will Arp)

- Improved port recovery support after a failed request upon a shutdown 
server.
- An init SQL string can now be specified for an opened port.

Doc, first of all, thanks for the driver. It is amazing. Secondly, 
I've tried a few ways to be able to detect for an open port without 
the program erroring out. I've just settled on a "error? attempt 
[db/locals]" Do you have any recommendations?

some nice performance tips http://www.scribd.com/doc/393792/MySQL-performance-coding

http://developers.slashdot.org/article.pl?sid=08/04/16/2337224long 
life postgresql 8)

Read the comments: The CEO of MySQL sets the story straight.

Does anyone experimented with the sphinx search engine? http://www.sphinxsearch.com/

Here's a handy function to test if a MySQL server is up :

mysql-alive?: func [/host ip /port n /local p][
	either attempt [
		p: open/direct/no-wait join tcp:// [
			either host [ip][127.0.0.1]
			#":"
			either port [n][3306]
		]
	][
		close p
		yes
	][
		no
	]
]

you can also use a simple : port: attempt [ open mysql://... ]

ok but your driver should reconnect automagically 8-)

Yes if the server is just restarted, no problem, the driver will 
reconnect.

You rock Doc. Thanks. That was an answer several years in the making!

Will, I have, and for Qtask I have even implemented the search protocol 
in REBOL. It's fast.

(i.e. the search client protocol that can talk to Sphinx). You can 
also compile a client in mysql and then use sql for your queries.

Maarten: that is great news! Do you plan on releasing it for the 
rest of us?

would there be any advantage having rebol as an external language 
plugin compared to Dock sheme?

http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures

bad question, but it would be cool to program stored procedures in 
rebol

here is a short and concise presentation about sphinx http://www.scribd.com/doc/2670976/Sphinx-High-Performance-Full-Text-Search-for-MySQL-Presentation

Will: I'll have to ask Reichart.

Please 8P

Guys, what do you think about Tim Johnson's ml remark? Could we help 
him somehow?

-----------------------------------

I've been using mysql-protocol (Mysql Scheme) and cgi for many years 
now. I 

really like it, but I think that /command would bring some advantages, 
if
it can directly access the "C" API for MySQL.


Example: Using the mysql scheme on a tcp/ip connection, I have to 
code

the escaping of special characters for insert and updates and code 
the
unescaping of special characters for retrieval. 


Using the C API from other scripting languages, the mysql_real_escape_string()
function handles the escaping _appropriate_ to the version, and
queries are similarly unescaped.

Isn't REBOL fast enough for most operations in regards to MySQL? 
Wouldn't it be sufficient to write such escaping fun in REBOL?

about escaping, Nenad's driver has such functions already, so i don't 
see that as an advantage for the native driver. Qtask has a native 
driver too (for speed reason with large record sets), but remember 
that when you use the mysql c library either your app has to be GPL 
or you need a license from MySQL (Qtask has a license).

I have a mysql wrapper that I use in production but haven't released 
because it need cleaning and docs, but if there is interest I could 
release for testing as is. id does things like:

print .db/get/all/sort/debug [mailing/m mailingLog/ml] [ml.dc m.email 
ml.url] [{ml._mailing=m.id}] [m.email asc ml.dc desc]

SELECT `ml`.`dc`,`m`.`email`,`ml`.`url` FROM `mailing` `m`,`mailingLog` 
`ml` WHERE ml._mailing=m.id ORDER BY `m`.`email`,`ml`.`dc` DESC
print .db/set/debug 'mailing 12 reduce['active false] 
UPDATE `mailing` SET `active`=0 WHERE id=12.0

print .db/get/all/flat/debug '_node_tree [_node _media] [{_tree=? 
AND _issue=?} 5443 22]

SELECT `_node`,`_media` FROM `_node_tree` WHERE _tree='5443' AND 
_issue='22'
..