long string concatenation /concatenar string largo

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

From Google Translate:

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(“SomethingElse = 1”)

rs = DB.SQLSelect(Join(arsSQL, " "))

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

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.

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

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.
had not noticed Kem mentioned this already :slight_smile:

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

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

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

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.

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

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

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?

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

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.

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

Kem and dave,

so use prepared statements? :stuck_out_tongue:

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

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

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

s="INSERT INTO mytable (values '$1','$2','$3')"

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

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

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.