[Gambas-user] MySQL query problems with apostropies

[nando]

Strings that contain these three characters: quote, double quote and backslash
need to be escaped.
You escape them by having a backslash immediately before it.
Example:
 Patty O'Lantern  would be...
 Patty O\'Lantern

One way is to make a small function that will insert a backslash
when it finds a quote or double quote or backslash. 

When you create your SQL string (Insert, Update, Select, etc)
you escape the strings for the char, varchar, text, or similar.

sql = "SELECT * FROM inventory WHERE part = ' & escape(mypart) & "';"

SELECT from the database will not return the 'escape' backslash.
It will appear normal.

Please note: / (on the question mark key) is not the backslash.
\ is the backslash.

-Fernando



---------- Original Message -----------
From: Keith Clark <keithclark at ...2185...>
To: gambas-user at lists.sourceforge.net
Sent: Tue, 06 Apr 2010 14:45:59 -0400
Subject: [Gambas-user] MySQL query problems with apostropies

> I have the following code:
> 
> $Query = ""
> QueryArray[0] = "insert into products_description"
> QueryArray[1] =
> "(products_id,language_id,products_name,products_description,products_format)"
> QueryArray[2] = "values (" & NewProductID & ",1,'" & TitleTextBox.Text &
> "','" & DescriptionTextArea.Text & "'," & FormatID & ")"
> LengthOfQuery = 2
> FOR QueryIndex = 0 TO LengthOfQuery
>         $Query = $Query & QueryArray[QueryIndex]
> NEXT 
> $Result = DatabaseConnection.$Con.Exec($Query)
> 
> Now, it works fine until I try to have a value in
> DescriptionTextArea.Text with punctuation like apostrophes and quotation
> marks in them.  I get an error in mysql query, always near the
> punctuation.
> 
> I imagine the same would happen with my value in TitleTextBox as well if
> it contained the same punctuation marks.
> 
> Any ideas that I'm probably just overlooking?
> 
> Keith
> 
> ------------------------------------------------------------------------------
> Download Intel® Parallel Studio Eval
> Try the new software tools for yourself. Speed compiling, find bugs
> proactively, and fine-tune applications for parallel performance.
> See why Intel Parallel Studio got high marks during beta.
> http://p.sf.net/sfu/intel-sw-dev
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
------- End of Original Message -------