LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 12-24-2006, 02:51 PM   #1
ihopeto
LQ Newbie
 
Registered: Jan 2006
Posts: 19

Rep: Reputation: 0
using perl to insert special characters into mysql database


simple questions but beyond my skill level.

$insert = "anything under the sun including@!#$%^&*()";

Now how do I set that up to insert into mysql table with PERL.

I have all the perl syntax working for putting in a simple string but this string won't work.

I use this statement

$myquery = "INSERT INTO $tablename (raw) VALUES (\"$insert\")";

Thanks

Brian
 
Old 12-24-2006, 06:28 PM   #2
theNbomr
LQ 5k Club
 
Registered: Aug 2005
Distribution: OpenSuse, Fedora, Redhat, Debian
Posts: 5,398
Blog Entries: 2

Rep: Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908
Quote:
Originally Posted by ihopeto
x
I have all the perl syntax working for putting in a simple string but this string won't work.
Perhaps you could better describe the meaning of "won't work"? Does the query reply with an error? Does the query silently fail, or insert something not expected? Does the program crash? Does it cause the database server to behave unpredictably? Some other form of calamity? Please be specific.

--- rod.
 
Old 12-24-2006, 08:37 PM   #3
ihopeto
LQ Newbie
 
Registered: Jan 2006
Posts: 19

Original Poster
Rep: Reputation: 0
additional info

As suggested....some additional information may be useful

Here is the error I get.

The error can vary depending on what characters are in the string being inserted....sometimes it even works(this string varies widely in the real situation) as is...but the @ character is never inserted correctly

I do not want to later the string in any way...just insert it as i get it.

The 'test' insert looked like this and yield the following errors:

$insert="te!@#$%^&*()_+|}{":<>?\][;',./st' entry insert#$@dog";

-------------------------------------------------------------
errors
-------------------------------------------------------------
Bareword found where operator expected at database_insert.pl line 25, near "',./st' entry"
(Missing operator before entry?)
syntax error at database_insert.pl line 25, near ""te!@#$%^&*()_+|}{":"
Unmatched right square bracket at database_insert.pl line 25, at end of line
Missing right curly or square bracket at database_insert.pl line 46, at end of line
Execution of database_insert.pl aborted due to compilation errors.
 
Old 12-25-2006, 03:52 AM   #4
theNbomr
LQ 5k Club
 
Registered: Aug 2005
Distribution: OpenSuse, Fedora, Redhat, Debian
Posts: 5,398
Blog Entries: 2

Rep: Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908
Okay, so in fact, you have not gotten the perl syntax working. Your errors are resulting from perl confusing the meaning of the special characters contained in your literal strings. You need to escape any character that perl can interpret in any of its special ways. You can escape them by inserting a backslash '\' in front of them. Alternatively, you can use the stronger "'" single-quote to quote your literal strings, which prevents any of the perl special characters from being interpretted by the compiler. Try this:
Code:
$insert='te!@#$%^&*()_+|}{":<>?\][;\',./st\' entry insert#$@dog';
These errors have nothing to do with SQL.
--- rod.
 
Old 12-25-2006, 01:28 PM   #5
ihopeto
LQ Newbie
 
Registered: Jan 2006
Posts: 19

Original Poster
Rep: Reputation: 0
That works OK if you have only one piece of data coming to you...but I can have 100's of lines of data every minute.

I was hoping for a solution that would cover all possiblities not a solution that has to be introduced line by line or character by character....the data I get can have anything in it as it may contain compressed(encoded) data.

Are you saying there is NO all encompassing set of brackets that will do the job and I must do a search and replace on every line of data?
 
Old 12-25-2006, 04:08 PM   #6
theNbomr
LQ 5k Club
 
Registered: Aug 2005
Distribution: OpenSuse, Fedora, Redhat, Debian
Posts: 5,398
Blog Entries: 2

Rep: Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908Reputation: 908
There is a distinction between literal strings that you are using as a test case, and string data input from a data stream or composed at runtime. The literal strings in your examples must be protected or escaped in order for the perl compiler to understand how to deal with them. Data injected into scalar variables at runtime is not similarly constrained. However, there may still be special characters that need to be properly treated within the context of an SQL query. Your DBMS docs should explain this. You should rewrite your testbed script so that the scalar strings you are testing are read from a disk file or from standard input, to avoid the complications related to the perl compiler.

--- rod.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
inserting special characters into mysql with bash script ihopeto Linux - Newbie 1 12-05-2006 12:46 PM
How do I insert special character with alt? Meriadoc Linux - Newbie 5 05-17-2006 10:42 AM
using sed to insert lines with special characters disorderly Linux - Software 26 04-20-2006 05:30 PM
how to insert special characters set2004 Linux - Newbie 2 08-05-2004 10:50 AM
How to interact with MYSQL database from within perl program Bassam Programming 5 02-26-2004 05:00 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 02:50 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration