www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - Swedish letters fuck up parsing into SQL querry

reply Anders S <anders xore.se> writes:
Hi guys,

I'm trying to read a name from a struct iorequest where the name 
is char name[20]
The struct is received through a FIFO pipe and message is going 
into a mysql database to update specific post there.

Now my problem is that all works fine to read and stop with  '\0' 
termination till I receive a Swedish character, ie åäö. Then the 
string gets crazy and reads all 20 chars no matter what.

Any ideas how to read all chars including åäö?

Using "~ to!string(name) ~" to build the SQL querry string as 
below

int extract_Cell_From_IOREQ(int CellIndex){
      auto sql =    "UPDATE celldata set
                 name='"~ to!string(cellTab[CellIndex].name) ~"', 
...
Mar 23 2020
parent reply Adam D. Ruppe <destructionator gmail.com> writes:
My first thought is to!string(cellTab[CellIndex].name) is wrong, 
if it is a char[20] you should be scanning it to find the length 
and slicing. Maybe [0 .. name.indexOf("\0")] or whatever.

You also shouldn't be building a query by concatenation.....
Mar 23 2020
parent reply Anders S <anders xore.se> writes:
On Monday, 23 March 2020 at 13:53:50 UTC, Adam D. Ruppe wrote:

 My first thought is to!string(cellTab[CellIndex].name) is 
 wrong, if it is a char[20] you should be scanning it to find 
 the length and slicing. Maybe [0 .. name.indexOf("\0")] or 
 whatever.

 You also shouldn't be building a query by concatenation.....


Hi, thks

do you mean I should loop through each pos till 
strlen(cellTab[CellIndex].name) to find "\0"?

How do you suggest I do the querry build then?
Mar 23 2020
next sibling parent reply bauss <jj_1337 live.dk> writes:
On Monday, 23 March 2020 at 14:26:46 UTC, Anders S wrote:

 On Monday, 23 March 2020 at 13:53:50 UTC, Adam D. Ruppe wrote:

 My first thought is to!string(cellTab[CellIndex].name) is 
 wrong, if it is a char[20] you should be scanning it to find 
 the length and slicing. Maybe [0 .. name.indexOf("\0")] or 
 whatever.

 You also shouldn't be building a query by concatenation.....


 Hi, thks

 do you mean I should loop through each pos till 
 strlen(cellTab[CellIndex].name) to find "\0"?

 How do you suggest I do the querry build then?


This is open to sql injection.

I thought we were rid of this in this day and age.

Use prepared statements.
Mar 23 2020
parent Anders S <anders xore.se> writes:
On Monday, 23 March 2020 at 14:58:03 UTC, bauss wrote:

 On Monday, 23 March 2020 at 14:26:46 UTC, Anders S wrote:

 On Monday, 23 March 2020 at 13:53:50 UTC, Adam D. Ruppe wrote:

 My first thought is to!string(cellTab[CellIndex].name) is 
 wrong, if it is a char[20] you should be scanning it to find 
 the length and slicing. Maybe [0 .. name.indexOf("\0")] or 
 whatever.

 You also shouldn't be building a query by concatenation.....


 Hi, thks

 do you mean I should loop through each pos till 
 strlen(cellTab[CellIndex].name) to find "\0"?

 How do you suggest I do the querry build then?


 This is open to sql injection.

 I thought we were rid of this in this day and age.

 Use prepared statements.


Yes true however I'm in early development and want to get a red 
line working, then take care of the issues ;)
Mar 23 2020
prev sibling parent reply Adam D. Ruppe <destructionator gmail.com> writes:
On Monday, 23 March 2020 at 14:26:46 UTC, Anders S wrote:

 do you mean I should loop through each pos till 
 strlen(cellTab[CellIndex].name) to find "\0"?


strlen is ok, that gives the answer itself. Just slice to that.

cellTab[CellIndex].name[0 .. strlen(cellTab[CellIndex].name.ptr)]

could do it. or

size_t end = 0;
foreach(idx, ch; cellTab[CellIndex].name)
    if(ch == 0) {
         end = idx;
         break;
    }

auto name = cellTab[CellIndex].name[0 .. end];


anything like that


 How do you suggest I do the querry build then?


how are you running it? using a lib or just generating a .sql 
file?
Mar 23 2020
parent reply Anders S <anders xore.se> writes:
On Monday, 23 March 2020 at 15:07:31 UTC, Adam D. Ruppe wrote:

 On Monday, 23 March 2020 at 14:26:46 UTC, Anders S wrote:

 do you mean I should loop through each pos till 
 strlen(cellTab[CellIndex].name) to find "\0"?


 strlen is ok, that gives the answer itself. Just slice to that.

 cellTab[CellIndex].name[0 .. 
 strlen(cellTab[CellIndex].name.ptr)]

 could do it. or

 size_t end = 0;
 foreach(idx, ch; cellTab[CellIndex].name)
    if(ch == 0) {
         end = idx;
         break;
    }

 auto name = cellTab[CellIndex].name[0 .. end];


 anything like that


 How do you suggest I do the querry build then?


 how are you running it? using a lib or just generating a .sql 
 file?


Hi,
I'm creating a connection to the db and conn.exec(sql)
I think I'll try the foreach to find out if it works .... ( 
tomorrow )
Mar 23 2020
next sibling parent reply Adam D. Ruppe <destructionator gmail.com> writes:
On Monday, 23 March 2020 at 15:15:12 UTC, Anders S wrote:

 I'm creating a connection to the db and conn.exec(sql)


It depends on the library but it is almost always easier to do it 
right than to do it the way you are.

like with my lib it is

db.query("update celldata set name = ?", new_name);
Mar 23 2020
parent reply matheus <matheus gmail.com> writes:
On Monday, 23 March 2020 at 15:41:50 UTC, Adam D. Ruppe wrote:

 On Monday, 23 March 2020 at 15:15:12 UTC, Anders S wrote:

 I'm creating a connection to the db and conn.exec(sql)


 It depends on the library but it is almost always easier to do 
 it right than to do it the way you are.

 like with my lib it is

 db.query("update celldata set name = ?", new_name);


I'm not the OP but I have a question, isn't this passive to SQL 
injection too, or your LIB will handle this somehow?

If is the later could you please point the code on GitHub?

Matheus.
Mar 24 2020
next sibling parent Steven Schveighoffer <schveiguy gmail.com> writes:
On 3/24/20 7:15 AM, matheus wrote:

 On Monday, 23 March 2020 at 15:41:50 UTC, Adam D. Ruppe wrote:

 On Monday, 23 March 2020 at 15:15:12 UTC, Anders S wrote:

 I'm creating a connection to the db and conn.exec(sql)


 It depends on the library but it is almost always easier to do it 
 right than to do it the way you are.

 like with my lib it is

 db.query("update celldata set name = ?", new_name);


 
 I'm not the OP but I have a question, isn't this passive to SQL 
 injection too, or your LIB will handle this somehow?


I haven't seen the code, but I'm going to guess this is using prepared 
statements with the given string as a parameter. This is what 
mysql-native does.

-Steve
Mar 24 2020
prev sibling parent reply WebFreak001 <d.forum webfreak.org> writes:
On Tuesday, 24 March 2020 at 11:15:24 UTC, matheus wrote:

 On Monday, 23 March 2020 at 15:41:50 UTC, Adam D. Ruppe wrote:

 On Monday, 23 March 2020 at 15:15:12 UTC, Anders S wrote:

 I'm creating a connection to the db and conn.exec(sql)


 It depends on the library but it is almost always easier to do 
 it right than to do it the way you are.

 like with my lib it is

 db.query("update celldata set name = ?", new_name);


 I'm not the OP but I have a question, isn't this passive to SQL 
 injection too, or your LIB will handle this somehow?

 If is the later could you please point the code on GitHub?

 Matheus.


https://github.com/mysql-d/mysql-native/blob/8f9cb4cd9904ade43af006f96e5e03eebe7a7c19/source/mysql/protocol/comms.d#L494

it's builtin into mysql
Mar 24 2020
parent Anders S <anders xore.se> writes:
On Tuesday, 24 March 2020 at 14:10:19 UTC, WebFreak001 wrote:

 On Tuesday, 24 March 2020 at 11:15:24 UTC, matheus wrote:

 On Monday, 23 March 2020 at 15:41:50 UTC, Adam D. Ruppe wrote:

 On Monday, 23 March 2020 at 15:15:12 UTC, Anders S wrote:

 I'm creating a connection to the db and conn.exec(sql)


 It depends on the library but it is almost always easier to 
 do it right than to do it the way you are.

 like with my lib it is

 db.query("update celldata set name = ?", new_name);


 I'm not the OP but I have a question, isn't this passive to 
 SQL injection too, or your LIB will handle this somehow?

 If is the later could you please point the code on GitHub?

 Matheus.


 https://github.com/mysql-d/mysql-native/blob/8f9cb4cd9904ade43af006f96e5e03eebe7a7c19/source/mysql/protocol/comms.d#L494

 it's builtin into mysql


Ahhh, thanks need to dig into this and learn.

Thanks guys for all the responses. Got plenty of leads to dig 
into, also issues I have to consider to be a better coder ;)
Thks again
Mar 24 2020
prev sibling parent WebFreak001 <d.forum webfreak.org> writes:
On Monday, 23 March 2020 at 15:15:12 UTC, Anders S wrote:

 On Monday, 23 March 2020 at 15:07:31 UTC, Adam D. Ruppe wrote:

 On Monday, 23 March 2020 at 14:26:46 UTC, Anders S wrote:

 do you mean I should loop through each pos till 
 strlen(cellTab[CellIndex].name) to find "\0"?


 strlen is ok, that gives the answer itself. Just slice to that.

 cellTab[CellIndex].name[0 .. 
 strlen(cellTab[CellIndex].name.ptr)]

 could do it. or

 size_t end = 0;
 foreach(idx, ch; cellTab[CellIndex].name)
    if(ch == 0) {
         end = idx;
         break;
    }

 auto name = cellTab[CellIndex].name[0 .. end];


 anything like that


 How do you suggest I do the querry build then?


 how are you running it? using a lib or just generating a .sql 
 file?


 Hi,
 I'm creating a connection to the db and conn.exec(sql)
 I think I'll try the foreach to find out if it works .... ( 
 tomorrow )


if you use mysql-native, use

conn.exec("UPDATE celldata SET name=?, ...", name);

where you can make a function for name =

/// Takes the data part from a fixed length string until a null 
terminator.
/// Returns: a slice of text until a null terminator or whole 
string in case there is none.
const(char)[] str(size_t n)(const(char)[n] text)
{
     // count until \0 (in bytes, so we can't cause utf decoding 
exception)
     auto end = text[].representation.countUntil(0);
     // return whole string if there is no \0, otherwise until \0
     return end == -1 ? text[] : text[0 .. end];
}

I think making your own function here instead of using to!string 
is what you want here. If you put in a char[20] into to!string, 
it will still return a string with the remaining characters being 
\0 characters.
Mar 24 2020