--------- Original Message --------
Da: "Dimitar Popov" <dp@basex.org>
To: "basex-talk@mailman.uni-konstanz.de" <basex-talk@mailman.uni-konstanz.de>
Oggetto: Re: [basex-talk] sql error
Data: 05/09/15 21:56

On Saturday, September 05, 2015 10:13:08 AM michele.greco2@email.it wrote:

> Hi,

 

Hi!

 

> where am i wrong?

 

1. you have an XQuery syntax error; instead of:

let $q:= "insert into person values("","||nome||","","","","","","")"

it should be (note the different quotation marks):

let $q:= "insert into person values(''," || $nome || ",'','','','','','')"

 

2. you have SQL injection [1] and performance issue by concatenating the value directly into the SQL statement; prepared statements [2] are in this case your friend:

 

declare namespace w="http://schemas.openxmlformats.org/wordprocessingml/2006/main";

 

declare function local:nome() as xs:string* {

(: I've no idea whatcha doin here pal... :)

for $document in collection("curriculum")

let $c:= document-uri($document)

order by $c

return

for $e in doc($c)//w:tc[.//text()="Nome"]

return $e/./following::text() except (

for $x in doc($c)//w:tc[.//text()="Indirizzo"]

return $x//following::text())

};

 

let $init := sql:init("com.mysql.jdbc.Driver"),

$conn := sql:connect("jdbc:mysql://localhost:3306/DbName","user","password"),

$stmt := sql:prepare($conn, "INSERT INTO person VALUES('',?,'','','','','')")

for $nome in local:nome()

return sql:execute-prepared($stmt,

<sql:parameters>

<sql:parameter type="string">{$nome}</sql:parameter>

</sql:parameters>)

 

Check the BaseX docs [3] for more info and come back if you need more help :)

 

Cheers,

Dimitar

 

 

[1] https://en.wikipedia.org/wiki/Sql_injection

[2] https://en.wikipedia.org/wiki/Prepared_statement

[3] http://docs.basex.org/wiki/SQL_Module