# long string concatenation /concatenar string largo **URL:** \ **Category:** General\ **Created:** [July 17, 2018, 2:36pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390 "2018-07-17T14:36:46Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![nicolás\_canessa](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/9de053/32.png) [@nicolás\_canessa](https://forum.xojo.com/u/nicol%C3%A1s_canessa)\ **Post date:** [July 17, 2018, 2:36pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/1 "2018-07-17T14:36:46Z")
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 ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [July 17, 2018, 2:45pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/2 "2018-07-17T14:45:01Z")
From Google Translate: > [@](#): > > I have a long sql query with many lines. what I want to do is save the query in a string. but since it is quite long, it is uncomfortable. I currently do the following: > > ` > > Is there a better or more practical way to achieve this? > > PS: I’ve already tried to put all the sql code in a constant, but I think it’s a bad idea because within the query I have values that change, for example the user, the date, etc. ---
**Author:** ![Tim\_Parnell](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_parnell/32/161_2.png) [@Tim\_Parnell](https://forum.xojo.com/u/Tim_Parnell)\ **Post date:** [July 17, 2018, 2:46pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/3 "2018-07-17T14:46:40Z")
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 🙂 ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [July 17, 2018, 2:48pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/4 "2018-07-17T14:48:49Z")
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](https://github.com/ktekinay/XOJO-SQLBuilder) to build the statement on the fly. But mostly, use a constant and prepared statements. ---
**Author:** ![nicolás\_canessa](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/9de053/32.png) [@nicolás\_canessa](https://forum.xojo.com/u/nicol%C3%A1s_canessa)\ **Post date:** [July 17, 2018, 2:57pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/5 "2018-07-17T14:57:14Z")
I didn’t know there were so many options. Thank you! ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [July 17, 2018, 4:04pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/6 "2018-07-17T16:04:33Z")
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 🙂 ```auto dim sql_string as string sql_string = _ "select * from....." + _ " left join..." + _ "where..." +_ " order by...." +_ " etc etc.." ``` ---
**Author:** ![Michel\_Bujardet](https://forum.xojo.com/user_avatar/forum.xojo.com/michel_bujardet/32/252_2.png) [@Michel\_Bujardet](https://forum.xojo.com/u/Michel_Bujardet)\ **Post date:** [July 18, 2018, 10:45am UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/7 "2018-07-18T10:45:21Z")
> [@396612:@nicolscanessa](#): > > tengo una consulta sql larga con muchas lineas. Favor de poner su pregunta en el Espanol canal. Se puede cambiar el circa del titulo. ---
**Author:** ![nicolás\_canessa](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/9de053/32.png) [@nicolás\_canessa](https://forum.xojo.com/u/nicol%C3%A1s_canessa)\ **Post date:** [July 19, 2018, 1:25pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/8 "2018-07-19T13:25:49Z")
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 ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [July 19, 2018, 1:28pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/9 "2018-07-19T13:28:20Z")
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. ---
**Author:** ![nicolás\_canessa](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/9de053/32.png) [@nicolás\_canessa](https://forum.xojo.com/u/nicol%C3%A1s_canessa)\ **Post date:** [July 19, 2018, 1:39pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/10 "2018-07-19T13:39:38Z")
yes kem… the query values does not include text supplied by the user in this particular query. I really appreciate your help. ---
**Author:** ![Richard\_Duke](https://forum.xojo.com/user_avatar/forum.xojo.com/richard_duke/32/246_2.png) [@Richard\_Duke](https://forum.xojo.com/u/Richard_Duke)\ **Post date:** [July 19, 2018, 1:53pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/11 "2018-07-19T13:53:39Z")
> [@396946:@nicolscanessa](#): > > 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. what i usually do is use [sql\_var\_username] and then do replaceable (str, [sql\_var\_username], value) ---
**Author:** ![nicolás\_canessa](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/9de053/32.png) [@nicolás\_canessa](https://forum.xojo.com/u/nicol%C3%A1s_canessa)\ **Post date:** [July 19, 2018, 1:58pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/12 "2018-07-19T13:58:24Z")
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? ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [July 19, 2018, 2:01pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/13 "2018-07-19T14:01:06Z")
> [@396957:@nicolscanessa](#): > > to avoid sql injection? use prepared statements… it will solve all of the issues you have stated, and will future proof your skillset ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [July 19, 2018, 2:02pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/14 "2018-07-19T14:02:05Z")
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. ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [July 19, 2018, 2:06pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/15 "2018-07-19T14:06:14Z")
and just in case nobody has previously mentioned this… Look in to the use of “prepared statements” ---
**Author:** ![brian\_franco](https://forum.xojo.com/user_avatar/forum.xojo.com/brian_franco/32/156_2.png) [@brian\_franco](https://forum.xojo.com/u/brian_franco)\ **Post date:** [July 19, 2018, 2:10pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/16 "2018-07-19T14:10:45Z")
Kem and dave, so use prepared statements? 😛 ---
**Author:** ![nicolás\_canessa](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/9de053/32.png) [@nicolás\_canessa](https://forum.xojo.com/u/nicol%C3%A1s_canessa)\ **Post date:** [July 19, 2018, 2:11pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/17 "2018-07-19T14:11:01Z")
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 ---
**Author:** ![nicolás\_canessa](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/9de053/32.png) [@nicolás\_canessa](https://forum.xojo.com/u/nicol%C3%A1s_canessa)\ **Post date:** [July 19, 2018, 2:14pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/18 "2018-07-19T14:14:55Z")
sorry for my ignorance, but sanitizing is not just about removing suspicious characters? or is it more than that? ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [July 19, 2018, 2:19pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/19 "2018-07-19T14:19:57Z")
Prepared Statements can actually be “easier” than what you are doing now, and safer ```auto 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 ```auto 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 > [@](#): > > The above code if for illustration purposes only, and do not constitute a ‘cut/paste’ solution, it is up to the developer to read, understand, and research how (or if) to incorporate any of this information into a production project > [@396964:@nicolscanessa](#): > > sorry for my ignorance, but sanitizing is not just about removing suspicious characters? or is it more than that? and prepared statements do everything required ---
**Author:** ![AlbertoD](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@AlbertoD](https://forum.xojo.com/u/AlbertoD)\ **Post date:** [July 19, 2018, 2:28pm UTC](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390/20 "2018-07-19T14:28:07Z")
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. [Next page](https://forum.xojo.com/t/long-string-concatenation-concatenar-string-largo/44390.md?page=2)