MySQL Sanitation

[zackrspv]

First off, what I need:

1. I want to be able to allow the user to enter whatever they want to enter (this is a random assortment of text from error messages to cryptic system communication messages).

2. I want to input that information into the MySQL database.

The issue:

If a user enters special characters, such as:

[15:33:01] username: asdfasdf 'notes' 2 % @#$@#$"@#$L"@L#$":@L#$"@<$@?>$@?>M$, and that's the "error" okay %head% <tag>

The script woudl automatically generate the INSERT INTO command:

INSERT INTO tickets (opentech_id,closetech_id,crm_id,crm_name,main_issue,notes,active,priority,assigned_tech,DTGO,DTG

C,DTGA,DTGX,xfer_dep,crc,DTGU,DTGE) VALUES('username','','asdf','asdf adf ','asdfa df"issue 1"','[15:33:01] username: asdfasdf 'notes' 2 % @#$@#$"@#$L"@L#$":@L#$"@<$@?>$@?>M$','1','3','','2008/08/15 18:33:01','','','','','','2008/08/15 18:33:01','0');

Which, as we can see, would faily, because of the multiple " and ' and other special characters.

What i could do:

Escape all special characters on INSERTION into the MySQL database with \'s; however that seems to be a very....bulky way of doing it. Especially when you consider that i'd have to create a strip slashes command to remove those from the notes as well to preserve readability. Not a very good processing way.

I could also just remove all special characters, but then it'd make some of the messages pointless, so can't do that.

What i'd like to know:

How can i preserve what the user types in, no matter what it is, and protect the mysql database at the same time, and still protect syntax as well. How can this be encapsulated?

My current code is irrelevant, by the way. It asks for information from the user (which you all know how to do), and then puts it into an INSERT INTO query string for the MySQL UDF's _AddRecord function. So showing my script really wouldn't help ya here, especially as most of y'all that would be helping me here, would already know how to ask for information and format it. lol. I can show parts of the script if you wish, but i just don't see a need to right now.

[DataKruncher]

I just opened up MS Access and created a query, and then put in all kinds of special characters and single quotes and double quotes into the "Field" line of the query design, then went to the View menu, and choose SQL View. When Access converted the design grid into SQL, it was able to handle all the special characters. As long as there are double quotes on both ends of the data, any quotes, or single quotes within the double quotes on the ends just get inserted as text. Do you ever use MS Access to design a query and then look at the SQL View to see how it gets converted? I think if you can simply find a way to make sure there are double quotes on both ends of the data it will work. I don't know if this helped. I'm not an expert in SQL, but I have run into the same problem with WHERE conditions in SQL statements. I know that with WHERE conditions that are filtering for text, the text needs to be in single quotes or it won't work, but the single quotes must be enclosed with double quotes for the WHERE statement to work. I'm curious to know if you find a solution.

[zackrspv]

I just opened up MS Access and created a query, and then put in all kinds of special characters and single quotes and double quotes into the "Field" line of the query design, then went to the View menu, and choose SQL View. When Access converted the design grid into SQL, it was able to handle all the special characters. As long as there are double quotes on both ends of the data, any quotes, or single quotes within the double quotes on the ends just get inserted as text. Do you ever use MS Access to design a query and then look at the SQL View to see how it gets converted? I think if you can simply find a way to make sure there are double quotes on both ends of the data it will work. I don't know if this helped. I'm not an expert in SQL, but I have run into the same problem with WHERE conditions in SQL statements. I know that with WHERE conditions that are filtering for text, the text needs to be in single quotes or it won't work, but the single quotes must be enclosed with double quotes for the WHERE statement to work. I'm curious to know if you find a solution.

So, your suggestion would be to change:

INSERT INTO tickets (opentech_id,closetech_id,crm_id,crm_name,main_issue,notes,active,priority,assigned_tech,DTGO,DTG


C,DTGA,DTGX,xfer_dep,crc,DTGU,DTGE) VALUES('username','','asdf','asdf adf ','asdfa df"issue 1"','[15:33:01] username: asdfasdf 'notes' 2 % @#$@#$"@#$L"@L#$":@L#$"@<$@?>$@?>M$','1','3','','2008/08/15 18:33:01','','','','','','2008/08/15 18:33:01','0');

Too:

INSERT INTO tickets (opentech_id,closetech_id,crm_id,crm_name,main_issue,notes,active,priority,assigned_tech,DTGO,DTG


C,DTGA,DTGX,xfer_dep,crc,DTGU,DTGE) VALUES("'username','','asdf','asdf adf ','asdfa df"issue 1"','[15:33:01] username: asdfasdf 'notes' 2 % @#$@#$"@#$L"@L#$":@L#$"@<$@?>$@?>M$','1','3','','2008/08/15 18:33:01','','','','','','2008/08/15 18:33:01','0'");

Correct? Or did i misunderstand ya?

[DataKruncher]

So, your suggestion would be to change:

INSERT INTO tickets (opentech_id,closetech_id,crm_id,crm_name,main_issue,notes,active,priority,assigned_tech,DTGO,DTG



C,DTGA,DTGX,xfer_dep,crc,DTGU,DTGE) VALUES('username','','asdf','asdf adf ','asdfa df"issue 1"','[15:33:01] username: asdfasdf 'notes' 2 % @#$@#$"@#$L"@L#$":@L#$"@<$@?>$@?>M$','1','3','','2008/08/15 18:33:01','','','','','','2008/08/15 18:33:01','0');

Too:

INSERT INTO tickets (opentech_id,closetech_id,crm_id,crm_name,main_issue,notes,active,priority,assigned_tech,DTGO,DTG



C,DTGA,DTGX,xfer_dep,crc,DTGU,DTGE) VALUES("'username','','asdf','asdf adf ','asdfa df"issue 1"','[15:33:01] username: asdfasdf 'notes' 2 % @#$@#$"@#$L"@L#$":@L#$"@<$@?>$@?>M$','1','3','','2008/08/15 18:33:01','','','','','','2008/08/15 18:33:01','0'");

Correct? Or did i misunderstand ya?

[DataKruncher]

The double quotes should be at the beginning and end of the data for the field. I think you put the double quotes all the way to the beginning and end of the list of all the data for all the fields.

This is what I'd change it to:

VALUES('username','','asdf','asdf adf ','asdfa df"issue 1"',"[15:33:01] username: asdfasdf 'notes' 2 % @#$@#$"@#$L"@L#$":@L#$"@<$@?>$@?>M$",'1','3','','2008/08/15 18:33:01','','','','','','2008/08/15 18:33:01','0');

I put double quotes in front of [15:33:01] and after >M$.

[zackrspv]

The double quotes should be at the beginning and end of the data for the field. I think you put the double quotes all the way to the beginning and end of the list of all the data for all the fields.

This is what I'd change it to:

VALUES('username','','asdf','asdf adf ','asdfa df"issue 1"',"[15:33:01] username: asdfasdf 'notes' 2 % @#$@#$"@#$L"@L#$":@L#$"@<$@?>$@?>M$",'1','3','','2008/08/15 18:33:01','','','','','','2008/08/15 18:33:01','0');

I put double quotes in front of [15:33:01] and after >M$.

Hum, i think I tried what you suggested:

INSERT INTO tickets (opentech_id,closetech_id,crm_id,crm_name,main_issue,notes,active,priority,assigned_tech,DTGO,DTG

C,DTGA,DTGX,xfer_dep,crc,DTGU,DTGE) VALUES("phillipr","","CRM ID","Test M'test"test","Test "issue" 'ok'","[13:51:29] phillipr: Wow, that's a test "issue"","1","3","","2008/08/17 16:51:29","","","","","","2008/08/17 16:51:29","0");

As we can see, I prefaced all of the 'value' fields with the " (double quote), but since the text still contained " (double quotes), MySQL didn't even process the request, just threw back an error in syntax message.

[therks]

Escaped characters don't keep the slashes. That's the whole purpose of escaping them.

INSERT INTO tableName (id, val) VALUES(20, "The man said \"Hello world!\" with great gusto.")

With a SELECT you should get back

The man said "Hello world!" with great gusto.

You don't need to write any stripslashes function, just an addslashes. That's how most of my MySQL insertions have always worked.