long string concatenation /concatenar string largo

nicolás_canessa · July 17, 2018, 2:36pm

tengo una consulta sql larga con muchas lineas. lo que quiero hacer es guardar la consulta en un string. pero como es bastante larga, es incomodo. actualmente hago lo siguiente:

dim sql_string as string sql_string = sql_string + "select * from....." sql_string = sql_string + "left join..." sql_string = sql_string + "where..." sql_string = sql_string + "order by...." sql_string = sql_string + "etc etc.."

hay una mejor o mas practica forma de lograr esto?

pd: ya he probado poner todo el codigo sql en una constante, pero creo que es una mala idea porque dentro de la consulta tengo valores que cambian, por ej el usuario, la fecha, etc

Kem_Tekinay · July 17, 2018, 2:45pm

From Google Translate:

Tim_Parnell · July 17, 2018, 2:46pm

A tip I picked up from Bob is similar to that, but is a little easier to read is to use an array and join it:

[code]dim arsSQL() as String
arsSQL.Append(“SELECT * FROM t_Table”)
arsSQL.Append(“WHERE User = ‘John Doe’”)
arsSQL.Append(“AND”)
arsSQL.Append(“SomethingElse = 1”)

rs = DB.SQLSelect(Join(arsSQL, " "))
[/code]

This example is kind of silly and bad, but you get the idea

Kem_Tekinay · July 17, 2018, 2:48pm

Put the query into a constant and use a Prepared Statement to fill in the values.

Or use an array and join the different segments at the end. (See Tim’s post above.)

Or put it all into one assignment and use “_” to split the statement across lines.

Or use my SQL builder to build the statement on the fly.

But mostly, use a constant and prepared statements.

nicolás_canessa · July 17, 2018, 2:57pm

I didn’t know there were so many options. Thank you!

DaveS · July 17, 2018, 4:04pm

if you want to show “formatted SQL” in your code (it makes it easier to read later)
do this instead

[quote=396616:@Kem Tekinay]Or put it all into one assignment and use “_” to split the statement across lines.
[/quote]
had not noticed Kem mentioned this already

dim sql_string as string
sql_string = _
"select * from....." + _
"   left join..." + _
"where..." +_
"  order by...." +_
"     etc etc.."

Michel_Bujardet · July 18, 2018, 10:45am

Favor de poner su pregunta en el Espanol canal. Se puede cambiar el circa del titulo.

nicolás_canessa · July 19, 2018, 1:25pm

First of all, I want to thank you all for your ideas
Although it may not be the ideal solution, it works for me in this particular situation and I leave a record in case it serves someone in the future
I have solved it in the following way…
1- I create a constant and assign it the value of the sql query. The sql query has the values replaced by special words, e.g. sql_var_username, sql_var_dateofbirth… etc.
2- I transfer the value of the constant to a variable (dim sql_string as string = kconstant_value)
3- use ReplaceAll to replace special words with the corresponding value

Kem_Tekinay · July 19, 2018, 1:28pm

As long as your query values do not include text supplied by a user, that’s a fine solution. If they do, use a prepared statement.

nicolás_canessa · July 19, 2018, 1:39pm

yes kem… the query values does not include text supplied by the user in this particular query. I really appreciate your help.

Richard_Duke · July 19, 2018, 1:53pm

what i usually do is use [sql_var_username] and then do replaceable (str, [sql_var_username], value)

nicolás_canessa · July 19, 2018, 1:58pm

i have a related question. maybe i have to start a new thread?
i want to manually sanitize user entered values. i suspect i have to use replaceall. wich characters do i have to delete to avoid sql injection?

DaveS · July 19, 2018, 2:01pm

use prepared statements… it will solve all of the issues you have stated, and will future proof your skillset

Kem_Tekinay · July 19, 2018, 2:02pm

Do not manually sanitize. Use a prepared statement. There really is no reason not to use a prepared statement.

Also, use a prepared statement.

Finally, use a prepared statement.

In summary, use a prepared statement.

DaveS · July 19, 2018, 2:06pm

and just in case nobody has previously mentioned this… Look in to the use of “prepared statements”

brian_franco · July 19, 2018, 2:10pm

Kem and dave,

so use prepared statements?

nicolás_canessa · July 19, 2018, 2:11pm

i really dont understand how to use prepared statments, even though I’ve read the documentation
I’m going to have to look at it carefully, to understand how it works

nicolás_canessa · July 19, 2018, 2:14pm

sorry for my ignorance, but sanitizing is not just about removing suspicious characters? or is it more than that?

DaveS · July 19, 2018, 2:19pm

Prepared Statements can actually be “easier” than what you are doing now, and safer

s="INSERT INTO mytable (values '$1','$2','$3')"
s=replaceall(s,"$1","xxx1")
s=replaceall(s,"$2","yyy2")
s=replaceall(s,"$3","yyy3")

for the above you would have to “sanitize” xx1,yy2 and yyy3 if they contained any “harmful” characters or sql injection

Dim ps As SQLitePreparedStatement
s="INSERT INTO mytable (values ?,?,?)
ps = DB_PROJECT.Prepare(s)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)  // new value
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)  // new value
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)  // new value
ps.SQLExecute("xxx1","yyy2","yyy3")

for the above, xxx1, yyy2 and yyy3 can be any text, with no need to worry if they contained “harmful” characters, or possible sql injection data

NOTE : 1st example requires single quotes, prepared statement does not

and prepared statements do everything required

AlbertoD · July 19, 2018, 2:28pm

Nicols, you can check the code that Xojo offers in Examples - Database - PreparedSQLStatement - SQLitePreapredStmt

Don’t worry, you will understand it and when it makes sense (takes some time and practice), then you will always use it.