Recent

Author Topic: MySQL and escaping Chars  (Read 12711 times)

jfreak53

  • New Member
  • *
  • Posts: 21
MySQL and escaping Chars
« on: May 30, 2011, 01:36:23 am »
Is there a way to escape special characters before inserting into a MySQL DB?  With MySQL I'm used to working with PHP and it has addslashes for this function.  I need something similar that adds a \ before a ' character and a bunch more characters.  And I don't want to have to use replace functions is something already exists to do this.

Any ideas?

Lacak2

  • Guest
Re: MySQL and escaping Chars
« Reply #1 on: May 30, 2011, 07:13:48 am »
IMO you do not need do escaping in your code. Escaping is done internaly for example in sqlDB. So you use for example:

SQLQuery1.Fields[0].AsString:='aaa''bbb''ccc';

And ' will be escaped when updates will be applied into database.

Or do you want work directly with mysql client library API ?
Then you can use: mysql_escape_string or mysql_real_escape_string

jfreak53

  • New Member
  • *
  • Posts: 21
Re: MySQL and escaping Chars
« Reply #2 on: May 30, 2011, 03:52:43 pm »
Well no actually I would rather not work directly with the API, I would rather use the built in components in Laz.  Problem is I am not talking about escaping "" characters.

I'm talking when, for instance, my insert query has a varchar text to insert, that get's encapsulated with 'text to insert'.  But what happens when the user who submitted this data has a ' in his text, ie:

Code: [Select]
'my text to insert is 'code' forever'
So I would have to escape the middle ' characters.  Hence the escaping function would be very very valuable here.

I would rather not move to using the API directly to be honest.  But if that is the only way short of manually finding characters and replacing then I think I'm sunk.

garlar27

  • Hero Member
  • *****
  • Posts: 652
Re: MySQL and escaping Chars
« Reply #3 on: May 30, 2011, 06:04:54 pm »
You have to use QuotedStr() function

jfreak53

  • New Member
  • *
  • Posts: 21
Re: MySQL and escaping Chars
« Reply #4 on: May 30, 2011, 07:33:48 pm »
That function is close to what I'm looking for, thanks.  But it's still lacking a bit.  I don't need them double quoted since that's a Pascal thing, the double quotes.  In MySQL quotes have to be escaped by a backslash before, so it would be \'

garlar27

  • Hero Member
  • *****
  • Posts: 652
Re: MySQL and escaping Chars
« Reply #5 on: May 30, 2011, 08:12:44 pm »
Dough!!  :o

I've got confused with SQLite  :-[ ... sorry ...

Are you sure that you don't want to use StringReplace(s, '''', '\''', [rfReplaceAll]);?

jfreak53

  • New Member
  • *
  • Posts: 21
Re: MySQL and escaping Chars
« Reply #6 on: May 30, 2011, 09:12:47 pm »
Well yes, really, that is what I was originally going to use.  Problem being that there are something like 20 or 30 control characters that have to be replaced like that.  And I was just hoping there was already a function so I didn't have to create one :(

Thanks though.

garlar27

  • Hero Member
  • *****
  • Posts: 652
Re: MySQL and escaping Chars
« Reply #7 on: May 30, 2011, 10:09:53 pm »
Well yes, really, that is what I was originally going to use.  Problem being that there are something like 20 or 30 control characters that have to be replaced like that.  And I was just hoping there was already a function so I didn't have to create one :(

Thanks though.

Well, if there is no function like that, and you end up doing it by your self, then you can send it to FPC/Lazarus developers as a contribution.

I never had the need to scape characters, thus I never searched for that functionality in SQLdb controls nor ZEOS. If I find something I'll post it here.

Lacak2

  • Guest
Re: MySQL and escaping Chars
« Reply #8 on: May 31, 2011, 07:14:09 am »
You do not need escape user input data when using in conjuction with sqlDB.

Use for example:
SQLQuery1.SQL.Text:='insert into mytable values(:varchar_field)';
SQLQuery1.ParamByName('varchar_field').AsString:=myedit.Text;
SQLQuery1.ExecSQL;

When you pass some string as param (or as field) then escaping will be done internaly and you do not need care about it.

jfreak53

  • New Member
  • *
  • Posts: 21
Re: MySQL and escaping Chars
« Reply #9 on: May 31, 2011, 02:54:59 pm »
Yes Lacak it's not so much about escaping for Laz, but if I don't escape MySQL itself won't accept the command since you have to run EXECSQL which counts on MySQL itself running the SQL query.  So SQL itself will dump on this for instance:
Code: [Select]
insert into table (row) values ('my value with 'these' characters')
As you can see when you execute it mysql will dump because it has double ' characters and no \ before the internal ones.  So it's more MySQL than Laz that is the problem.

Well garlar, I think I might just have to make my own function ha ha ha.  Oh well, I'll go to the MySQL Docs and find out what all the escape chars are internally and make a replace function with all of them :(

Thanks!  :)

Lacak2

  • Guest
Re: MySQL and escaping Chars
« Reply #10 on: June 01, 2011, 07:11:51 am »
I still does not understand why you do not want pass user input as parameter into SQL command ?

If you have for example eUserInput=class(TEdit) then you can do:
Code: [Select]
SQLQuery1.SQL.Text:='insert into table (row) values (:user_input)';
SQLQuery1.ParamByName('user_input').AsString:=eUserInput.Text;
SQLQuery1.ExecSQL;
This will work, but if you do for exampe:
Code: [Select]
SQLQuery1.SQL.Text:=format('insert into table (row) values (%s)', [eUserInput.Text]);
SQLQuery1.ExecSQL;
this will fail (if eUserInput will contain unescaped quotes).

So can you provide your code, which fails?

garlar27

  • Hero Member
  • *****
  • Posts: 652
Re: MySQL and escaping Chars
« Reply #11 on: June 01, 2011, 04:54:07 pm »
I still does not understand why you do not want pass user input as parameter into SQL command ?

If you have for example eUserInput=class(TEdit) then you can do:
Code: [Select]
SQLQuery1.SQL.Text:='insert into table (row) values (:user_input)';
SQLQuery1.ParamByName('user_input').AsString:=eUserInput.Text;
SQLQuery1.ExecSQL;
This will work, but if you do for exampe:
Code: [Select]
SQLQuery1.SQL.Text:=format('insert into table (row) values (%s)', [eUserInput.Text]);
SQLQuery1.ExecSQL;
this will fail (if eUserInput will contain unescaped quotes).

So can you provide your code, which fails?

I think it's because he might want to include more than one command. Something like:

Code: [Select]
BEGIN TRANSACTION;
INSERT INTO table (rows) VALUES (values);
INSERT INTO table (rows) VALUES (values);
INSERT INTO table (rows) VALUES (values);
INSERT INTO table (rows) VALUES (values);
INSERT INTO table (rows) VALUES (values);
INSERT INTO table (rows) VALUES (values);
INSERT INTO table (rows) VALUES (values);
INSERT INTO table (rows) VALUES (values);
COMMIT;

And I don't know if you can pass different params for all INSERTs.

jfreak53

  • New Member
  • *
  • Posts: 21
Re: MySQL and escaping Chars
« Reply #12 on: June 13, 2011, 02:10:12 pm »
I still does not understand why you do not want pass user input as parameter into SQL command ?

If you have for example eUserInput=class(TEdit) then you can do:
Code: [Select]
SQLQuery1.SQL.Text:='insert into table (row) values (:user_input)';
SQLQuery1.ParamByName('user_input').AsString:=eUserInput.Text;
SQLQuery1.ExecSQL;
This will work, but if you do for exampe:
Code: [Select]
SQLQuery1.SQL.Text:=format('insert into table (row) values (%s)', [eUserInput.Text]);
SQLQuery1.ExecSQL;
this will fail (if eUserInput will contain unescaped quotes).

So can you provide your code, which fails?

Quite simple, if I did this:

Code: [Select]
SQLQuery1.SQL.Text:='insert into table (row) values (:user_input)';
SQLQuery1.ParamByName('user_input').AsString:=eUserInput.Text;
SQLQuery1.ExecSQL;

And the userinput was:

Quote
This is my 'input', isn't it cool.

That would fail, since the input field would have to be this:

Code: [Select]
SQLQuery1.SQL.Text:='insert into table (row) values (''':user_input''')';
Since it's text and I'm inserting into a text or Varchar field in MySQL you have to quote.  The only way to get around it is to use "" characters.  But if the user used "" Character instead of singles then it would also fail.  See the problem, no matter what I do in Laz it is still subject to the Laws and rules of the MySQL server since all the component does is execute the command in mysql, and those rules like any other programming language state you must escape control characters inside a quoted sentence to keep from errors.

Thanks for your comments.  I think I might just have to make my own  :( oh well.

 

TinyPortal © 2005-2018