MySQL and escaping Chars lazarus / 16-Databases ---------------------------------------------------------------------- jfreak53 · 2011-05-29 23:36 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 · 2011-05-30 05:13 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 · 2011-05-30 13:52 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 · 2011-05-30 16:04 You have to use QuotedStr() function ---------------------------------------------------------------------- jfreak53 · 2011-05-30 17:33 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 · 2011-05-30 18:12 Dough!!  I've got confused with SQLite  ... sorry ... Are you sure that you don't want to use StringReplace(s, '''', '\''', [rfReplaceAll]);? ---------------------------------------------------------------------- jfreak53 · 2011-05-30 19:12 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 · 2011-05-30 20:09 Quote from: jfreak53 on May 30, 2011, 09:12:47 pmWell 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 · 2011-05-31 05:14 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 · 2011-05-31 12:54 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 · 2011-06-01 05:11 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 · 2011-06-01 14:54 Quote from: Lacak2 on June 01, 2011, 07:11:51 amI 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 · 2011-06-13 12:10 Quote from: Lacak2 on June 01, 2011, 07:11:51 amI 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: QuoteThis 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.